Re: [BUGS] 8.4: COPY continues after client disconnects
On 01.03.2013 17:15, Jon Nelson wrote: Using PostgreSQL 8.4.13 on ScientificLinux 6.3 (x86_64), I noticed that a pg_dump ran out of (local) disk space. However, the server was still using CPU and disk resources. An strace clearly showed this pattern: read() = 8192 sendto(...) = -1 EPIPE -- SIGPIPE (Broken pipe) @ The server does detect the broken pipe. It logs the following messages: 637 LOG: 08006: could not send data to client: Connection reset by peer 638 LOCATION: internal_flush, pqcomm.c:1108 639 STATEMENT: COPY ... to stdout; 640 LOG: 08006: could not send data to client: Broken pipe 641 LOCATION: internal_flush, pqcomm.c:1108 642 STATEMENT: COPY ... to stdout; This was fixed in version 9.2. Per release notes: Cancel the running query if the client gets disconnected (Florian Pflug) If the backend detects loss of client connection during a query, it will now cancel the query rather than attempting to finish it. COPY counts as a "running query". -- - Heikki -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #7924: Trigger update function don't take correct values
The following bug has been logged on the website: Bug reference: 7924 Logged by: Maria Email address: mv.gonza...@cir.es PostgreSQL version: Unsupported/Unknown Operating system: RedHat Description: Hi! I have a trigger that when you update a value on a table, makes an insert into another table. The trigger looks like the following: BEGIN set datestyle to 'sql'; set datestyle to 'european'; IF (TG_OP = 'UPDATE' and (OLD.situation!=NEW.situation)) THEN INSERT INTO table_B VALUES (OLD.id,NEW.situation,NOW()); END IF; RETURN NULL; END; For exemple, If I do the next querys: --> update table_A set situation='Started' where id='23'; On table_A appears: id| situation +--- 23| Started On table_B appears: id| situation | when +---+- 23| Started | 2013-03-08 12:33:35 --> update table_A set situation='Waiting' where id='23'; On table_A appears: id| situation +--- 23| Waiting On table_B appears: id| situation | when +---+- 23| Started | 2013-03-08 12:33:35 23| Started | 2013-03-08 12:33:37 I don't understand why after the second update, the insert done on table_B is incorrect, and has the same values that the first one. I've been looking for some kind of explanation like cache or something, but I couldn't find anything. Thanks in advance. Maria -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7924: Trigger update function don't take correct values
"mv.gonza...@cir.es" wrote: > On table_B appears: > id | situation | when > +---+- > 23 | Started | 2013-03-08 12:33:35 > 23 | Started | 2013-03-08 12:33:37 > > I don't understand why after the second update, the insert done on table_B > is incorrect, and has the same values that the first one. > > I've been looking for some kind of explanation like cache or something, but > I couldn't find anything. It helps to provide a self-containted test case like this: test=# CREATE TABLE table_a (id int PRIMARY KEY, situation text NOT NULL); CREATE TABLE test=# INSERT INTO table_a VALUES (23, 'Unknown'); INSERT 0 1 test=# CREATE TABLE table_b (id int NOT NULL, sitation text NOT NULL, "when" timestamptz NOT NULL); CREATE TABLE test=# CREATE FUNCTION log_func() test-# RETURNS TRIGGER test-# LANGUAGE plpgsql test-# AS $$ test$# BEGIN test$# set datestyle to 'sql'; test$# set datestyle to 'european'; test$# IF (TG_OP = 'UPDATE' and (OLD.situation!=NEW.situation)) THEN test$# INSERT INTO table_B VALUES (OLD.id,NEW.situation,NOW()); test$# END IF; test$# RETURN NULL; test$# END; test$# $$; CREATE FUNCTION test=# CREATE TRIGGER log_trig test-# AFTER UPDATE ON table_a test-# FOR EACH ROW EXECUTE PROCEDURE log_func(); CREATE TRIGGER test=# update table_A set situation='Started' where id='23'; UPDATE 1 test=# select * from table_a; id | situation +--- 23 | Started (1 row) test=# select * from table_b; id | sitation | when +--+ 23 | Started | 08/03/2013 13:01:08.169213 CST (1 row) test=# update table_A set situation='Waiting' where id='23'; UPDATE 1 test=# select * from table_a; id | situation +--- 23 | Waiting (1 row) test=# select * from table_b; id | sitation | when +--+ 23 | Started | 08/03/2013 13:01:08.169213 CST 23 | Waiting | 08/03/2013 13:01:08.179754 CST (2 rows) How about showing us yours? -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Nested xmlagg doesn't give a result 9.2.3
Just to let everyone know that I haven't forgotten this. I just don't have any time to spend on it. The problem still occurs. 2013/2/20 Peter Kroon > I've migrated everything to Linux and I'm able to continue my work. > I'll get back on this next week. > > > 2013/2/19 Lou Picciano > >> Sorry, Peter - I think I'd suggest something different. Start at the >> beginning; do this testing directly from the CLI (psql) on each of the >> machines, and compare results... I'd be curious, specifically, to see if >> you see any of those string length restrictions you've alluded to when >> using the CLI. >> >> At this point, it's not clear whether you're testing (various versions?) >> of pgAdmin(?) or (various versions?) of libpq? >> >> >> Lou Picciano >> >> - Original Message - >> From: Peter Kroon >> To: Pavel Stehule >> Cc: Lou Picciano , Michael Paquier < >> michael.paqu...@gmail.com>, pgsql-bugs@postgresql.org >> Sent: Tue, 19 Feb 2013 16:50:25 - (UTC) >> Subject: Re: [BUGS] Nested xmlagg doesn't give a result 9.2.3 >> >> >you can test your queries from pgAdmin SQL executor >> I have already done so. >> >> I'll look into the logs. >> >> >> 2013/2/19 Pavel Stehule >> >>> >>> 2013/2/19 Peter Kroon : >>> >>try to use pgAdminIII >>> >>> > >>> >>> > Could you be more specific? >>> >>> you can test your queries from pgAdmin SQL executor >>> >>> >>> but it is strange error - try to look to postgresql and system logs >>> >>> >>> Pavel >>> >>> >>> > >>> >>> > >>> >>> > 2013/2/19 Pavel Stehule >>> >>> >> >>> >>> >> 2013/2/19 Peter Kroon : >>> >>> >> > Where can I check and/or alter this? >>> >>> >> >>> >>> >> try to use pgAdminIII >>> >>> >> >>> >>> >> Regards >>> >>> >> >>> >>> >> Pavel >>> >>> >> >>> >>> >> > >>> >>> >> > >>> >>> >> > 2013/2/19 Lou Picciano >>> >>> >> >> >>> >>> >> >> I wonder if there's a difference in the implementation(s) of >>> readline >>> >>> >> >> buffering? >>> >>> >> >> >>> >>> >> >> >>> >>> >> >> - Original Message - >>> >>> >> >> From: Peter Kroon >>> >>> >> >> To: Lou Picciano >>> >>> >> >> Cc: Michael Paquier , >>> >>> >> >> pgsql-bugs@postgresql.org >>> >>> >> >> Sent: Tue, 19 Feb 2013 15:28:47 - (UTC) >>> >>> >> >> Subject: Re: [BUGS] Nested xmlagg doesn't give a result 9.2.3 >>> >>> >> >> >>> >>> >> >> Exceeding length 4679 is a problem. Query results(length) equal or >>> >>> >> >> below >>> >>> >> >> this number succeed. >>> >>> >> >> >>> >>> >> >> >>> >>> >> >> 2013/2/19 Peter Kroon >>> >>> >> >>> >>> >>> >> >>> When there are more then 88 rows in the table like 595 I can run >>> the >>> >>> >> >>> query with success when using: WHERE id BETWEEN 1 AND 88; >>> >>> >> >>> >>> >>> >> >>> Using LIMIT 88 fails -> returns nothing >>> >>> >> >>> Selecting all fails as well. >>> >>> >> >>> >>> >>> >> >>> >>> >>> >> >>> 2013/2/19 Peter Kroon >>> >>> >> >>> >>> >> When there are in __table_to_table more than 88 rows nothing gets >>> >>> >> returned, otherwise the query rolls out fine. >>> >>> >> >>> >>> >> >>> >>> >> >>> >>> >> 2013/2/19 Peter Kroon >>> >>> >> > >>> >>> >> > It appears to be a Windows issue only. >>> >>> >> > I'll try to post some code. >>> >>> >> > >>> >>> >> > >>> >>> >> > 2013/2/19 Lou Picciano >>> >>> >> >> >>> >>> >> >> Seems your testing from different environments like that could >>> >>> >> >> easily >>> >>> >> >> add any mix of libpq client libraries into the equation (??) >>> >>> >> >> (Are both test machines running the same version of pgAdmin? >>> and >>> >>> >> >> are >>> >>> >> >> both connecting using the libpq installed with them?) >>> >>> >> >> >>> >>> >> >> We have plenty of experience with clients reporting varying >>> >>> >> >> behavior >>> >>> >> >> from our 'applications', when it turns out they've 'hooked >>> into' an >>> >>> >> >> unexpected version of the libpq client without, for example, >>> SSL >>> >>> >> >> support >>> >>> >> >> built in, or Kerberos, or... This often happens after the >>> client >>> >>> >> >> has >>> >>> >> >> unwittingly modified his environment in some way, sometimes >>> after >>> >>> >> >> installing >>> >>> >> >> software. >>> >>> >> >> >>> >>> >> >> While the 'support libraries' issues above have no bearing on >>> your >>> >>> >> >> case, of course, I certainly don't know enough to know that the >>> >>> >> >> different >>> >>> >> >> versions of libpq don't present xmlagg output differently! >>> >>> >> >> >>> >>> >> >> The experts here will weigh in. >>> >>> >> >> >>> >>> >> >> Lou Picciano >>> >>> >> >> >>> >>> >> >> >>> >>> >> >> - Original Message - >>> >>> >> >> From: Peter Kroon >>> >>> >> >> >>> >>> >> >> >>> >>> >> >> >>> >>> >> >> >>> >>> >> >> Sent: Tue, 19 Feb 2013 11:52:37 - (UTC) >>> >>> >> >> Subject: Re: [BUGS] Nested xmlagg doesn't give a result 9.2.3 >>> >>> >>
[BUGS] Issue with range types and casts?
select version(); version -- PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit create table tenmin as select * from sampledata where collect_ts <@ '[2013-01-01 00:00:00,2013-01-01 00:10:00)'; ERROR: could not find range type for data type timestamp with time zone Time: 0.189 ms This seems like it ought to be fixable. Postgres has figured out that it needs to find the range type for timestamptz. Why can't it? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] New kind of crash?
Folks, This is one I've never seen before: => select generate_master_tables(); WARNING: AbortTransaction while in COMMIT state PANIC: cannot abort transaction 9387287, it was already committed PANIC: cannot abort transaction 9387287, it was already committed The connection to the server was lost. Attempting reset: Failed. Time: 42259.753 ms !> Interestingly, Postges came back up on its own after about a minute and seems to be OK. The stored procedure in question is one which creates about 600 tables. This actually exceeds max_locks_per_transaction, but what happened above is not the normal failure for that. Note that the tables were, in fact, created, and as far as I can tell there's no corruption of the database. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] New kind of crash?
Do you have any non default procedural languages installed? I provoked exactly that error with a similar script which used a PL/R procedure (see BUGS thread "PL/R Median Busts Commit"...the cause is signal hi-jacking in that case). Regards Mark On 09/03/13 13:27, Josh Berkus wrote: Folks, This is one I've never seen before: => select generate_master_tables(); WARNING: AbortTransaction while in COMMIT state PANIC: cannot abort transaction 9387287, it was already committed PANIC: cannot abort transaction 9387287, it was already committed The connection to the server was lost. Attempting reset: Failed. Time: 42259.753 ms !> Interestingly, Postges came back up on its own after about a minute and seems to be OK. The stored procedure in question is one which creates about 600 tables. This actually exceeds max_locks_per_transaction, but what happened above is not the normal failure for that. Note that the tables were, in fact, created, and as far as I can tell there's no corruption of the database. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] New kind of crash?
Josh Berkus wrote: > Folks, > > This is one I've never seen before: > > => select generate_master_tables(); > WARNING: AbortTransaction while in COMMIT state > PANIC: cannot abort transaction 9387287, it was already committed Anything that causes an ERROR in the final stages of a transaction commit will look like this. Maybe, for example, something tried to acquire more shared memory for something (serializable xact?) but that was already full because of lock objects. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
