Re: [BUGS] Psql or test application hangs when interface is down for the DB server
Currently the test application or the psql will unblock after ~15 minutes. This is a very huge time to realize for programs this situation which do database updates. As far as I have debugged, I see that the execution is waiting on 'poll()' system call in the function pqSocketPoll() which is called as a result of 'PQexec()' and the timeout paramater provided will be -1, which means infinite wait time. It not clear how this is getting unblocked after 15 minutes. Who will write to the socket or who will interrupt the poll() system call? Is there any other workaround or alternative so that the situation about the interface is down is known and based on that the 'PQexec' does not get blocked for ~15 minutes. regards, Niranjan -Original Message- From: ext Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 15, 2008 8:16 PM To: K, Niranjan (NSN - IN/Bangalore) Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] Psql or test application hangs when interface is down for the DB server "K, Niranjan (NSN - IN/Bangalore)" <[EMAIL PROTECTED]> writes: > In the postgres database there is table 'COUNTER_TABLE' with column > integer type 'COUNTER'. The test application attached in this mail, > will start a transaction, gets the current value in the COUNTER, > increments the value and updates the incremented value into the COUNTER column. > This is being done in a loop. The program is started in a remote > client and after few transactions, the interface between the client & > the database server is brought down (example I used "ifconfig eth0 > down" in the server). With this the test application hangs and does > not return from the API of postgres (ex. 'PQexec'). If you waited long enough for the TCP connection to time out, it would return (with an error, of course). This behavior is not a bug, it is the expected behavior of any program using a network connection. regards, tom lane -- 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 #4307: INSERT fails with primary key contraint
"Oskars Ozols" <[EMAIL PROTECTED]> writes:
> id bigint NOT NULL DEFAULT
> nextval(('public.event_log_id_seq'::text)::regclass),
> 2008-07-15 12:32:03 EEST STATEMENT: INSERT INTO public.event_log
> (date_time, ip_address, action_type, severity, parameters, web_address,
> server, user_id, id) VALUES ('2008-07-15 12:28:50.00',
> '123.123.123.123', 'WebServices.SomeService:LogError', 7, 'error text',
> 'http://123.123.123.123/WebServices/SomeService.asmx', '4', 75, 156112)
There's something strange here. Your SQL statement includes the id as a
literal constant 156112. This isn't the normal way to write this query. This
is defeating the point of the DEFAULT you see in the table definition.
Postgres guarantees that the nextval() function will only return each value
once. But it's not clear from this log how your application is generating the
156112 value which it is explicitly putting in the query. If it's getting it
by calling nextval() then it's somehow using it twice.
It's also possible someone has written code to pick primary key values by
calling "select max(id)+1". That is guaranteed to have race conditions like
this.
The safest thing to do is to just leave out the id column from your INSERT
statement. Just let the DEFAULT expression generate a value for you. Then you
can use curval('event_log_id_seq') to find out what value it generated.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!
--
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 #4307: INSERT fails with primary key contraint
"Oskars Ozols" <[EMAIL PROTECTED]> writes: > I have noticed that during high load Postgre starts to use old free sequence > values (like 156112 in my sample). Sometimes it's ok, but sometimes it fails > with error above. This is fairly hard to believe; the sequence code was debugged years ago. Particularly seeing that your application is evidently supplying the id value for itself in the INSERT (from a previous nextval, or perhaps some other way?), it seems much more likely that there's a bug on the application side. If you'd like us to pursue this, please put together a self-contained test case. Assuming it's real, perhaps a simple custom script for pgbench would serve to show the problem. regards, tom lane -- 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] Psql or test application hangs when interface is down for the DB server
"K, Niranjan (NSN - IN/Bangalore)" <[EMAIL PROTECTED]> writes: > In the postgres database there is table 'COUNTER_TABLE' with column > integer type 'COUNTER'. The test application attached in this mail, will > start a transaction, gets the current value in the COUNTER, increments > the value and updates the incremented value into the COUNTER column. > This is being done in a loop. The program is started in a remote client > and after few transactions, the interface between the client & the > database server is brought down (example I used "ifconfig eth0 down" in > the server). With this the test application hangs and does not return > from the API of postgres (ex. 'PQexec'). If you waited long enough for the TCP connection to time out, it would return (with an error, of course). This behavior is not a bug, it is the expected behavior of any program using a network connection. regards, tom lane -- 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 #4307: INSERT fails with primary key contraint
The following bug has been logged online:
Bug reference: 4307
Logged by: Oskars Ozols
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.3
Operating system: SuSE Linux Enterprise Server
Description:INSERT fails with primary key contraint
Details:
I have following table for event log:
CREATE TABLE event_log
(
id bigint NOT NULL DEFAULT
nextval(('public.event_log_id_seq'::text)::regclass),
user_id integer,
date_time timestamp(0) without time zone,
ip_address character varying(15) NOT NULL,
action_type character varying(500) NOT NULL,
severity integer NOT NULL,
parameters text,
web_address character varying(160),
server character(1),
CONSTRAINT event_log_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
During high load (processor load ~95%) of different SELECT/INSERT requests
this table starts to give following errors in db log:
2008-07-15 12:32:03 EEST ERROR: duplicate key value violates unique
constraint "event_log_pkey"
2008-07-15 12:32:03 EEST STATEMENT: INSERT INTO public.event_log
(date_time, ip_address, action_type, severity, parameters, web_address,
server, user_id, id) VALUES ('2008-07-15 12:28:50.00',
'123.123.123.123', 'WebServices.SomeService:LogError', 7, 'error text',
'http://123.123.123.123/WebServices/SomeService.asmx', '4', 75, 156112)
You may notice the difference in time when message was written to db log
(12:32:04) and actual time of event (12:28:50).
Currently there are ~3 million rows in event_log. Old records are regulary
deleted (autovacuum is on, too). During high peak it's possible that 20
events are finished to be written to event_log in 1 sec.
Current Start value for sequence event_log_id_seq is 8536444.
I have noticed that during high load Postgre starts to use old free sequence
values (like 156112 in my sample). Sometimes it's ok, but sometimes it fails
with error above.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] full-text search doesn't fall back on sequential scan when it could
Hi,
I am running PostgreSQL 8.3.3 on Linux 2.6.18
[EMAIL PROTECTED]:~/z$ uname -a
Linux io.mills.lan 2.6.18-6-vserver-k7 #1 SMP Sat Jun 7 01:10:29 UTC
2008 i686 GNU/Linux
Short version of the problem:
When I run a full-text search that requires a full scan of the table,
an error is returned (GIN indexes don't support sequential scan)
instead of falling back on a sequential scan which would return
the results of the query.
Long version:
I have a database with two tables named 'one' and 'two':
z1=> \d one
Table "public.one"
Column | Type | Modifiers
+--+---
a | text |
Indexes:
"fts_a" gin (to_tsvector('simple'::regconfig, a))
z1=> \d two
Table "public.two"
Column | Type | Modifiers
+--+---
b | text |
Indexes:
"fts_b" gin (to_tsvector('simple'::regconfig, b))
Table 'one' has 51 rows:
z1=> select * from one;
a
--
Two
Three
Four
Five
Forty nine
Fifty
Fifty one
Fifty two
(51 rows)
Table 'two' has 5001 rows:
z1=> select * from two;
b
Fifty three
Fifty four
Fifty five
Fifty six
Five thousand fifty
Five thousand fifty one
Five thousand fifty two
Five thousand fifty three
(5001 rows)
(At the bottom of this email I have copied the commands
I used to create the database.)
Now I run a full-text query on table 'one':
z1=> select count(*) from one where to_tsvector('simple', a) @@
to_tsquery('simple', '!Four');
count
---
47
(1 row)
Running the same query under 'explain analyze' shows that the index is not being
used, but a sequential scan is being done:
z1=> explain analyze select a from one where to_tsvector('simple', a)
@@ to_tsquery('simple', '!Four');
QUERY PLAN
---
Seq Scan on one (cost=0.00..1.77 rows=1 width=32) (actual
time=0.019..0.309 rows=47 loops=1)
Filter: (to_tsvector('simple'::regconfig, a) @@ '!''four'''::tsquery)
Total runtime: 0.341 ms
(3 rows)
That all works fine.
But all is not fine when I do the same thing on the longer table 'two':
z1=> \set VERBOSITY verbose
z1=> select count(*) from two where to_tsvector('simple', b) @@
to_tsquery('simple', '!Four');
ERROR: 0A000: query requires full scan, which is not supported by GIN indexes
LOCATION: gin_extract_tsquery, tsginidx.c:74
I understand that this query does require a full scan, and I understand that
GIN indexes don't support a full scan, but why couldn't the planner fall back
to a sequential scan in this case? Of course it's slower, but I would prefer a
slower answer than failure with an error and no answer at all. I can simulate
this solution by doing the following, which forces a sequential scan.
z1=> set enable_bitmapscan to off;
SET
z1=> set enable_indexscan to off;
SET
z1=> select count(*) from two where to_tsvector('simple', b) @@
to_tsquery('simple', '!Four');
count
---
3277
(1 row)
z1=> explain analyze select count(*) from two where
to_tsvector('simple', b) @@ to_tsquery('simple', '!Four');
QUERY PLAN
-
Aggregate (cost=114.03..114.04 rows=1 width=0) (actual
time=91.171..91.171 rows=1 loops=1)
-> Seq Scan on two (cost=0.00..114.02 rows=5 width=0) (actual
time=0.028..89.598 rows=3277 loops=1)
Filter: (to_tsvector('simple'::regconfig, b) @@ '!''four'''::tsquery)
Total runtime: 91.208 ms
(4 rows)
Any thoughts? Is this something that could be fixed in a future version of
PostgreSQL? Or is the current behavior intentionally the way it is for
some reason I haven't thought of yet?
Thanks a lot for your help,
James Dietrich
P.S. Here is the procedure I used create the test database.
I can also provide the output of pg_dump upon request.
[EMAIL PROTECTED]:~/z$ psql template1 -U stariadmin -W
Password for user stariadmin:
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
template1=> create database z1;
CREATE DATABASE
template1=> \q
[EMAIL PROTECTED]:~/z$ psql z1 -U stariadmin -W -f a_commands
Password for user stariadmin:
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE SEQUENCE
CREATE LANGUAGE
CREATE FUNCTION
nextval
-
1
(1 row)
CREATE FUNCTION
CREATE FUNCTION
fill_one
--
done
(1 row)
fill_two
--
done
(1 row)
[EMAIL PROTECTED]:~/z$ cat a_commands
create table one(a text);
create index fts_a on one using gin(to_tsvector('simple', a));
create table two(b text);
create index fts_b on two using gin(to_tsvector('simple', b));
create sequence sequ;
create language plpgsql;
create or replac
[BUGS] Psql or test application hangs when interface is down for the DB server
Hi, Environment used: Postgres 8.3.1 psqlODBC 08.03.0200 Testcase: In the postgres database there is table 'COUNTER_TABLE' with column integer type 'COUNTER'. The test application attached in this mail, will start a transaction, gets the current value in the COUNTER, increments the value and updates the incremented value into the COUNTER column. This is being done in a loop. The program is started in a remote client and after few transactions, the interface between the client & the database server is brought down (example I used "ifconfig eth0 down" in the server). With this the test application hangs and does not return from the API of postgres (ex. 'PQexec'). <> In another example, run the psql from the remote client and connect to the database server. Execute the SQL to update the COUNTER_TABLE. After successful execution, next bring the network interface down on the database server (Ex. I use the command "ifconfig eth0 down") and next execute the SQL command to update the COUNTER_TABLE again from the same remote client and the same DB session. The SQL command hangs. regards, Niranjan pg_test_app.cpp Description: pg_test_app.cpp -- 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 #4186: set lc_messages does not work
Tom Lane wrote: > Magnus Hagander <[EMAIL PROTECTED]> writes: > > Thomas H. wrote: > >> so at least that explains the "changed" behaviour. nevertheless, > >> LC_MESSAGES seems to be defunct - with the "locale" folder present, > >> pg always picks the os' language and ignores the lc_message value. > > > This looks like I can reproduce though, at least on cvs head. Did this > > work for you in previous versions? > > Maybe we were using a different build of gettext in the previous > releases, one that didn't look at the same info as the current code? > > Anyway the patch mentioned at the start of the thread > http://archives.postgresql.org/pgsql-patches/2008-02/msg00038.php > purports to fix this. It doesn't seem to have gotten reviewed > though. Agreed. Magnus, someone, can we get feedback on the patch at this URL? http://archives.postgresql.org/pgsql-patches/2008-02/msg00038.php -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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 #4296: Server crashes by restoring database
=?iso-8859-2?q?Micha=B3_Szel=B1g?= <[EMAIL PROTECTED]> writes: > Dnia poniedzia³ek, 14 lipca 2008, Tom Lane napisa³: >> You still haven't given anywhere near enough information to identify the >> bug. I wonder though if any of these tables involve non-built-in >> datatypes? An out-of-date .so file for an add-on datatype could easily >> lead to crashes in these operations. > I have composite types in my dump, there are some arrays of them too. There > are no over datatypes. We're not going to be able to do much about this bug report unless you can provide a test case. There's just not enough information here to even begin looking for a cause. regards, tom lane -- 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 #4296: Server crashes by restoring database
Dnia poniedziałek, 14 lipca 2008, Tom Lane napisał: > =?iso-8859-2?q?Micha=B3_Szel=B1g?= <[EMAIL PROTECTED]> writes: > > the crash occurs by this command in my dump: > > > > COPY users_history (users_history_id, users_id, uh_date, > > uh_php_session_id, uh_gpsc, uh_ip, uh_browser, uh_referer, uh_url) FROM > > stdin; > > > > so, i excluded this table from my dump and the same error occurs by this > > command: > > ALTER TABLE ONLY invoices_lines ADD CONSTRAINT invoices_lines_pkey > > PRIMARY KEY (nagid, o, linid); > > You still haven't given anywhere near enough information to identify the > bug. I wonder though if any of these tables involve non-built-in > datatypes? An out-of-date .so file for an add-on datatype could easily > lead to crashes in these operations. > > regards, tom lane I have composite types in my dump, there are some arrays of them too. There are no over datatypes. earlier i had messages by restoring this database like this: Consider increasing the configuration parameter "checkpoint_segments". checkpoints are occurring too frequently (17 seconds apart). So i have increased this parameter and shared memory, but the crash stays. Best regards, M.Sz. -- == Michał Szeląg tel.: +48 693 11 37 55 -- 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 #4286: ORDER BY returns inconsistent results when using LIMIT on a integer column set to default values
Hi, Thank you for your kind answers and for not having killed me despite the fact that I report a bu^H^H documented feature. It had no impact on me since there's an easy workaround to this behaviour (like you reported), I just really thought it was a bug... Sorry guys, Thibauld 2008/7/8 Tom Lane <[EMAIL PROTECTED]>: > "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: >> Thibauld Favre wrote: >>> Here's what I get as a result on my server. See how 'a' is systematically >>> put at the end of the result set until the LIMIT clause reaches the value 8. >>> Above 8, the results get consistent again. > >> Doesn't look like a bug to me. > > It isn't; in fact this behavior is specifically disclaimed in the docs > (http://www.postgresql.org/docs/8.3/static/sql-select.html#SQL-LIMIT): > >Thus, using different LIMIT/OFFSET values to select different >subsets of a query result *will give inconsistent results* >unless you enforce a predictable result ordering with ORDER BY. > > Since the query's ORDER BY isn't sufficient to constrain the row > ordering, the observed behavior is covered by this statement. > > (FWIW, it's unlikely there's any plan change involved here. What I > think is happening is that the first row is being selected as the > quicksort pivot item.) > >regards, tom lane > -- 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 #4306: TSearch2 stemming, stop words and lexize behaviour inconsistent
The following bug has been logged online:
Bug reference: 4306
Logged by: Yishai Lerner
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.3.1
Operating system: RHEL5 and MacOSX 10.4
Description:TSearch2 stemming, stop words and lexize behaviour
inconsistent
Details:
I would expect the behavior for to_tsquery for the three variations of
"what", "what's" and "whats" to be consistent and for all variations to be
ignored since they all result in a stop word of "what". However, this is
not the case as to_tsquery("whats") returns the stop word "what" as a
result. Even more confusing is that if one were to look at the lexize
results below, they are inconsistent with the to_tsquery results below.
This seems like a bug to me.
goodrec_2=# select lexize('en_stem', 'what''s');
lexize
{what}
goodrec_2=# select lexize('en_stem', 'whats');
lexize
{what}
goodrec_2=# select lexize('en_stem', 'what');
lexize
{}
goodrec_2=# select to_tsquery('what''s');
NOTICE: query contains only stopword(s) or doesn't contain lexeme(s),
ignored
to_tsquery
goodrec_2=# select to_tsquery('whats');
to_tsquery
'what'
goodrec_2=# select to_tsquery('what');
NOTICE: query contains only stopword(s) or doesn't contain lexeme(s),
ignored
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
