sequence's plpgsql

On Wednesday 24 September 2003 17:40, Tim McAuley wrote:

Hi,

I've hit a little problem and was wondering if anyone might be able to
give some help.

Set-up:

- JBoss appserver using entity beans to access database
- Using sequence pattern to update primary keys for several tables.
Basically it is a method of getting primary keys without hitting the
database. It actually hits the database every 10th go and updates the
counter by 10 in the database.

You might want to try just using sequences - PG does some caching of generated
values for you. Sorry - can't remember how you alter the cache size, but try
SELECT * FROM my_sequence;
to see the details of its settings.

Use sequences, and from your sequence-holding class do something like:
SELECT nextval('myseq'),nextval('myseq'),...10 times...
That will give you a block of 10 sequence values in one go, and off you go.

If you'd rather have the values in one column, create a single-column table
"seq_count" and populate with values 1..10 then:
SELECT nextval('myseq'),seq_count.id FROM seq_count;

That any use?
--
Richard Huxton
Archonet Ltd

Richard Huxton wrote:

On Wednesday 24 September 2003 17:40, Tim McAuley wrote:

Hi,

I've hit a little problem and was wondering if anyone might be able to
give some help.

Set-up:

- JBoss appserver using entity beans to access database
- Using sequence pattern to update primary keys for several tables.
Basically it is a method of getting primary keys without hitting the
database. It actually hits the database every 10th go and updates the
counter by 10 in the database.

You might want to try just using sequences - PG does some caching of generated
values for you. Sorry - can't remember how you alter the cache size, but try
SELECT * FROM my_sequence;
to see the details of its settings.

Use sequences, and from your sequence-holding class do something like:
SELECT nextval('myseq'),nextval('myseq'),...10 times...
That will give you a block of 10 sequence values in one go, and off you go.

If you'd rather have the values in one column, create a single-column table
"seq_count" and populate with values 1..10 then:
SELECT nextval('myseq'),seq_count.id FROM seq_count;

That any use?

Thanks for that. I investigated using your suggestion but actually
discovered that these tables were only being updated by the stored
procedures so this meant it was safe to use sequences there. I have now
set these up however I am now getting "deadlock detection" errors.

I see from a previous email on the list that someone was able to get
decent debug out of the locks, i.e.

Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected

Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits
for AccessExclusiveLock on relation 18028 of database 17140; blocked by

How can I get this? I must be overlooking something because I've set the debug level to debug5 and still only get a single message saying
ERROR: deadlock detected
DEBUG: AbortCurrentTransaction

I've switched from using Postgresql 7.3.2 on a linux server to 7.3.4 running on my own machine through cygwin.

I've got output from "select * from pg_locks;" but am not getting very far with this. The only locks marked as false do not give a table oid, only the transaction id. i.e.
| | 11515901 | 30440 | ShareLock | f

Will continue working away to see if I can locate the deadlock.

Tim

Tim McAuley <mcauleyt@tcd.ie> writes:

I see from a previous email on the list that someone was able to get
decent debug out of the locks, i.e.

Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected
Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits
for AccessExclusiveLock on relation 18028 of database 17140; blocked by

How can I get this?

This display is a new feature in 7.4. Can you try your problem on a
7.4 beta release?

regards, tom lane

I see from a previous email on the list that someone was able to get
decent debug out of the locks, i.e.

Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected
Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits
for AccessExclusiveLock on relation 18028 of database 17140; blocked by

How can I get this?

This display is a new feature in 7.4. Can you try your problem on a
7.4 beta release?

Ah, that would make sense.

Another question now. I am unable to compile Postgresql 7.4 beta 3 under
cygwin (Windows 2K, using cgyipc 2).

I am getting the error:
"
creating information schema... ERROR: end-of-copy marker does not match
previous newline style
CONTEXT: COPY FROM, line 361
"

Any ideas?

Thanks,

Tim

Tim McAuley <mcauleyt@tcd.ie> writes:

Another question now. I am unable to compile Postgresql 7.4 beta 3 under
cygwin (Windows 2K, using cgyipc 2).

I am getting the error:
"
creating information schema... ERROR: end-of-copy marker does not match
previous newline style
CONTEXT: COPY FROM, line 361
"

That's interesting. COPY is complaining because the \. terminator in
the file it's been fed has a different kind of newline after it than
the newlines earlier in the file (LF vs CR/LF, no doubt). The part
of the initdb script that must be causing this is

echo "COPY information_schema.sql_features (feature_id, feature_name, sub_feature_id, sub_feature_name, is_supported, comments) FROM STDIN;"
cat "$datadir"/sql_features.txt
echo "\."
) \
| "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
echo "ok"

so it appears that cygwin's "echo" generates a different newline style
than what got put into sql_features.txt. A possible way to fix this is
to put the "\." line into sql_features.txt, but maybe there's a cleaner
answer. Peter, any thoughts?

regards, tom lane

On Fri, 2003-09-26 at 11:01, Tom Lane wrote:

so it appears that cygwin's "echo" generates a different newline style
than what got put into sql_features.txt. A possible way to fix this is
to put the "\." line into sql_features.txt, but maybe there's a cleaner
answer. Peter, any thoughts?

Does cygwin still have the install time option of what type of line feed
to use? I know at one point (a long time ago) when I installed cygwin,
and chose windows line feeds (CRLF) that it caused problems with several
applications. So the problem might be that with CYGWIN you could have
either type of line feed depending on what the user selected during
install.

On Fri, Sep 26, 2003 at 11:01:34AM -0400, Tom Lane wrote:

I am getting the error:
"
creating information schema... ERROR: end-of-copy marker does not match
previous newline style
CONTEXT: COPY FROM, line 361
"

That's interesting. COPY is complaining because the \. terminator in
the file it's been fed has a different kind of newline after it than
the newlines earlier in the file (LF vs CR/LF, no doubt). The part

Well actually I'm not so sure. I tried installing 7.4 from Oliver's
Debian packages and experienced exactly the same. We have yet to find
out why, but I doubt newlines changed between his Debian system and mine.

of the initdb script that must be causing this is

echo "COPY information_schema.sql_features (feature_id, feature_name, sub_feature_id, sub_feature_name, is_supported, comments) FROM STDIN;"
cat "$datadir"/sql_features.txt
echo "\."
) \
| "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely
echo "ok"

so it appears that cygwin's "echo" generates a different newline style
than what got put into sql_features.txt. A possible way to fix this is
to put the "\." line into sql_features.txt, but maybe there's a cleaner
answer. Peter, any thoughts?

Putting the "\." line into sql_features.txt did help me, too.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Tom Lane writes:

so it appears that cygwin's "echo" generates a different newline style
than what got put into sql_features.txt. A possible way to fix this is
to put the "\." line into sql_features.txt, but maybe there's a cleaner
answer. Peter, any thoughts?

There's no clean answer to this on Cygwin. This specific case is just a
little problem that we could solve locally, but in general you'll just end
up annoying people if you require them to use consistent line endings on
Cygwin.

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut wrote:

Tom Lane writes:

so it appears that cygwin's "echo" generates a different newline style
than what got put into sql_features.txt. A possible way to fix this is
to put the "\." line into sql_features.txt, but maybe there's a cleaner
answer. Peter, any thoughts?

There's no clean answer to this on Cygwin. This specific case is just a
little problem that we could solve locally, but in general you'll just end
up annoying people if you require them to use consistent line endings on
Cygwin.

This error is coming from the new 7.4 COPY code that allows \r\n as a
line terminator. Requiring the end-of-line to be consistent seemed to
be the only way to be sure we were not eating a literal carriage return
in the data stream. Let's put the "\." into sql_features.txt and see if
that fixes it, or can we use echo -c "\.\n" in initdb?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane writes:

so it appears that cygwin's "echo" generates a different newline style
than what got put into sql_features.txt. A possible way to fix this is
to put the "\." line into sql_features.txt, but maybe there's a cleaner
answer. Peter, any thoughts?

There's no clean answer to this on Cygwin. This specific case is just a
little problem that we could solve locally, but in general you'll just end
up annoying people if you require them to use consistent line endings on
Cygwin.

Yeah, I was wondering whether you wouldn't propose dropping the newline
consistency check. I'm not very comfortable with that, but maybe we
should. Bruce?

regards, tom lane

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane writes:

so it appears that cygwin's "echo" generates a different newline style
than what got put into sql_features.txt. A possible way to fix this is
to put the "\." line into sql_features.txt, but maybe there's a cleaner
answer. Peter, any thoughts?

There's no clean answer to this on Cygwin. This specific case is just a
little problem that we could solve locally, but in general you'll just end
up annoying people if you require them to use consistent line endings on
Cygwin.

Yeah, I was wondering whether you wouldn't propose dropping the newline
consistency check. I'm not very comfortable with that, but maybe we
should. Bruce?

I posted on that a few minutes ago. Yea, we can drop it, but we risk
eating carraige returns as data values. I am not sure how consistently
we output literal carriage returns in old dumps, nor how many apps
produce on literal carriage returns in COPY. If we conditionally eat
them, we run the risk of discarding some of their data without warning.
Perhaps we can throw a warning rather than an error, and adjust initdb
to be consistent.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

I posted on that a few minutes ago. Yea, we can drop it, but we risk
eating carraige returns as data values. I am not sure how consistently
we output literal carriage returns in old dumps, nor how many apps
produce on literal carriage returns in COPY. If we conditionally eat
them, we run the risk of discarding some of their data without warning.
Perhaps we can throw a warning rather than an error, and adjust initdb
to be consistent.

Would the best longterm solution be to require escaping CR in a data
value? (Yes I know this might cause backwards compatibility problems, at
least for a while).

cheers

andrew

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Perhaps we can throw a warning rather than an error, and adjust initdb
to be consistent.

I like the idea of reducing the newline consistency check to a warning.
There is one thing we'd have to watch for though (it's already an issue
but would become a bigger one): client-side COPY code had better be
prepared to absorb backend Notice messages while processing COPY IN.
Currently libpq doesn't read input data at all during a COPY IN loop,
which means that if the COPY generates more than a few K of warning
messages, the backend gets blocked on a full pipe and the whole
operation locks up. I have been meaning to fix that in libpq anyway,
but what other client libraries might have the same issue? Anyone know
whether JDBC would need a similar fix?

regards, tom lane

Andrew Dunstan wrote:

Bruce Momjian wrote:

I posted on that a few minutes ago. Yea, we can drop it, but we risk
eating carraige returns as data values. I am not sure how consistently
we output literal carriage returns in old dumps, nor how many apps
produce on literal carriage returns in COPY. If we conditionally eat
them, we run the risk of discarding some of their data without warning.
Perhaps we can throw a warning rather than an error, and adjust initdb
to be consistent.

Would the best longterm solution be to require escaping CR in a data
value? (Yes I know this might cause backwards compatibility problems, at
least for a while).

Yes, we do have that documented, but we supported it in earlier
releases, so we can't be sure who is using it, and it probably exists in
older dumps.

The particular problem is not literal carriage returns, which we don't
support any more, but carriage returns that happen to be at the end of
the line, right up against \n. If we relax this, we will conditionally
strip off the \r.

In fact, we can do that now:

create table xx(text);

Right now, if all the column value end with \r, we will silently eat it.
But if some have \r and some do not, we will throw an error. (Above,
when I say \r, I mean literal carriage return, not the "\r" string,
which we have always handled cleanly.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Perhaps we can throw a warning rather than an error, and adjust initdb
to be consistent.

I like the idea of reducing the newline consistency check to a warning.
There is one thing we'd have to watch for though (it's already an issue
but would become a bigger one): client-side COPY code had better be
prepared to absorb backend Notice messages while processing COPY IN.
Currently libpq doesn't read input data at all during a COPY IN loop,
which means that if the COPY generates more than a few K of warning
messages, the backend gets blocked on a full pipe and the whole
operation locks up. I have been meaning to fix that in libpq anyway,
but what other client libraries might have the same issue? Anyone know
whether JDBC would need a similar fix?

Wow, that sounds big. The ERROR will only happen once, while the
WARNING could happen a lot --- we could add code to throw the WARNING
only once per COPY command --- that would probably make sense. I don't
see how we could get all clients to handle this for 7.4, particularly
clients from previous releases.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Peter Eisentraut wrote:

Tom Lane writes:

so it appears that cygwin's "echo" generates a different newline style
than what got put into sql_features.txt. A possible way to fix this is
to put the "\." line into sql_features.txt, but maybe there's a cleaner
answer. Peter, any thoughts?

There's no clean answer to this on Cygwin. This specific case is just a
little problem that we could solve locally, but in general you'll just end
up annoying people if you require them to use consistent line endings on
Cygwin.

Here is a little diff to make initdb behave if we decide to keep the
COPY check.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

... we could add code to throw the WARNING
only once per COPY command --- that would probably make sense.

Seems like a bit of a kluge, but perhaps the best compromise. It would
be quite likely that you'd get the same warning on many lines of a COPY,
and that probably isn't really going to help people.

regards, tom lane

On Fri, 26 Sep 2003, Bruce Momjian wrote:

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Tom Lane writes:

so it appears that cygwin's "echo" generates a different newline style
than what got put into sql_features.txt. A possible way to fix this is
to put the "\." line into sql_features.txt, but maybe there's a cleaner
answer. Peter, any thoughts?

There's no clean answer to this on Cygwin. This specific case is just a
little problem that we could solve locally, but in general you'll just end
up annoying people if you require them to use consistent line endings on
Cygwin.

Yeah, I was wondering whether you wouldn't propose dropping the newline
consistency check. I'm not very comfortable with that, but maybe we
should. Bruce?

I posted on that a few minutes ago. Yea, we can drop it, but we risk
eating carraige returns as data values. I am not sure how consistently
we output literal carriage returns in old dumps, nor how many apps
produce on literal carriage returns in COPY. If we conditionally eat
them, we run the risk of discarding some of their data without warning.
Perhaps we can throw a warning rather than an error, and adjust initdb
to be consistent.

I'm running into issues where 7.4's pg_dump/pg_dumpall from a 7.2 database
to a 7.4beta3 database is producing some errors like this:

ERROR: literal newline found in data
HINT: Use "\n" to represent newline.
CONTEXT: COPY FROM, line 59

ERROR: literal carriage return found in data
HINT: Use "\r" to represent carriage return.
CONTEXT: COPY FROM, line 41

These show up with little or no context, only the "line number" of the
dump file. Since I'm wrapping these up in pg_dumpall, I don't have the
dump file so I don't know where the error is really occuring. It would be
nice to have such occurances echo the table / row they are getting the
error on, or maybe just the first 20 or so characters, so they'd be easier
to identify.

Is this related to this issue?

Tom Lane writes:

Yeah, I was wondering whether you wouldn't propose dropping the newline
consistency check. I'm not very comfortable with that, but maybe we
should. Bruce?

I don't mind if we keep it on pure-POSIX platforms. But one of the nicer
developments on Windows in recent(?) times is that you can actually use
any kind of line separator and most programs still work correctly (with
the notable exception of Notepad). The drawback of this is that you never
really know what kind of line separator a program is actually going to
write, especially when you start mixing Cygwin, MinGW, and native. So
this newline consistency check is not only going to be pretty annoying on
Windows, it's going to cancel a feature of the operating system
environment.

--
Peter Eisentraut peter_e@gmx.net