Re: [BUGS] psql or pgbouncer bug?
> The auto-reconnect behavior is long-established and desirable. What's > not desirable is continuing with any statements remaining on the same > line, I think. We need to flush the input buffer on reconnect. So if I understand it correctly, if I need correct transaction behaviour in psql even in case of disconnection the only safe way is to use one statement per line. Is this correct? Thanks, Kuba -- 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 #5469: regexp_matches() has poor behaviour and more poor documentation
The following bug has been logged online: Bug reference: 5469 Logged by: Daniele Varrazzo Email address: daniele.varra...@gmail.com PostgreSQL version: 8.4 Operating system: any Description:regexp_matches() has poor behaviour and more poor documentation Details: regexp_matches() has been recently discussed (http://archives.postgresql.org/pgsql-bugs/2010-04/msg00026.php): it is a setof function and as such it can drop results. Unfortunately it is an useful function to newcomers who use SQL, use regexps but don't arrive to read the chapter 34.4.7. (i.e. Extending SQL -> Query Language (SQL) Functions -> SQL Functions Returning Sets) and are not so enlightened to know that "setof text[]" means "if it doesn't match, it drops the record". They just expect the function to be a LIKE on steroids. Please describe the behavior in the documentation of the function (i.e. table 9-6. and section 9.7.3), possibly provide a function with a saner interface, i.e. returning a text[] of the first match or NULL on no match, or document a workaround (suitable for an user knowing regexps but not setof-returning functions) to make the function not dropping record (e.g. I fixed the "bug" adding a "|" at the end of the pattern, so that the function returns an array of NULL in case of no match: I don't think it is a trivial workaround). -- 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 pgbouncer bug?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 >> The auto-reconnect behavior is long-established and desirable. What's >> not desirable is continuing with any statements remaining on the same >> line, I think. We need to flush the input buffer on reconnect. > So if I understand it correctly, if I need correct transaction behaviour > in psql even in case of disconnection the only safe way is to use one > statement per line. > > Is this correct? Yes, that is correct. Pretty big gotcha. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201005240925 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkv6fncACgkQvJuQZxSWSsipbQCg3Cn6Hh4Uk9i2TwaKNgzB1Xef apIAoLiNoJT4pjtA4xaZXL11XdgUYwph =MF9l -END PGP SIGNATURE- -- 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 pgbouncer bug?
Jakub Ouhrabka writes: > So if I understand it correctly, if I need correct transaction behaviour > in psql even in case of disconnection the only safe way is to use one > statement per line. In existing releases, yes. Having put the BEGIN and UPDATE all on one line was a necessary precondition for this 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 pgbouncer bug?
Hi Jakub,
On 24/05/10 08:52, Jakub Ouhrabka wrote:
> The auto-reconnect behavior is long-established and desirable. What's
> not desirable is continuing with any statements remaining on the same
> line, I think. We need to flush the input buffer on reconnect.
So if I understand it correctly, if I need correct transaction
behaviour in psql even in case of disconnection the only safe way is
to use one statement per line.
You'd have to pay close attention to the responses if you go for that
option, personally I wouldn't recommend it - much safer to use \set
autocommit false, and that way you'll only ever get transactions
committed when you explicitly issue a commit.
Since the connection could drop at any point during a psql session, the
following sequence would also end up with some unwanted steps committed
automatically:
begin;
update table set col = X;
-- connection drops after above two statements complete - not important
whether they're on separate lines --
update table set col = Y; -- this statement will use current autocommit
behaviour
rollback; -- "no transaction in progress" message if autocommit was enabled
If you happen to miss the reconnection message during the above
sequence, you'll inadvertently be back in autocommit mode - so the 3rd
statement will be committed immediately.
Compare this to:
\set autocommit false
update table set col = X;
update table set col = Y;
rollback;
If the connection drops at any point before or after those statements,
the new connection will still be in transactional (manual commit) mode,
so there's no chance of any of the above statements being committed
(either the rollback on disconnect, or the explicit rollback will take
place).
Personally I always use '\set autocommit false' under psql, since it's
closer in behaviour to the Perl DBI ->connect(... { AutoCommit => 0 })
behaviour I'm used to. I'd definitely never risk using 'begin' in psql
with multiple statements.
Tom
--
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 #5470: EXTRACT(epoch from ...) missing last digit
The following bug has been logged online: Bug reference: 5470 Logged by: Randy Solomonson Email address: pgsqlb...@solomonson.com PostgreSQL version: 8.4.2 Operating system: Red Hat 4.1.2-46 (64-bit) Description:EXTRACT(epoch from ...) missing last digit Details: When converting from Timestamp with Time zone to seconds since epoch, the last digit of the fraction of a second is missing. Try the following query: SELECT x,EXTRACT(epoch from x),EXTRACT(microseconds from x)/100 FROM (SELECT '2010-05-24 11:38:01.701845'::timestamp x)t Here are the results: x |date_part | ?column? +--+-- 2010-05-24 11:38:01.701845 | 1274719081.70184 | 1.701845 Notice the date_part ends in 70184 and drops the sixth digit (a "5" in this case). Oddly enough, the last digit was also not rounded up. However, it is rounded up in some cases (eg .538505) -- 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 #5470: EXTRACT(epoch from ...) missing last digit
"Randy Solomonson" writes: > When converting from Timestamp with Time zone to seconds since epoch, the > last digit of the fraction of a second is missing. The result of extract() is float8, so you can't really expect it to have more than about 15 digits of precision. 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] xml data type implications of no =
Today I ran into some interesting consequences of the xml data type being without an "=" operator. One I thought I'd post here because it has a *possible* planner impact. I'm not sure it is actually a bug as such, but this seemed the best forum to post in initially: test=# \d bug Table "public.bug" Column | Type | Modifiers +-+--- id | integer | val| xml | test=# explain select val::text from bug; QUERY PLAN -- Seq Scan on bug (cost=0.00..58127.78 rows=1000278 width=32) Note the width estimate. However a more realistic estimate for width is: test=# select 8192/(reltuples/relpages) as width from pg_class where relname='bug'; width -- 394.130431739976 So we are going to massively underestimate the "size" of such a dataset. Now this appears to be a consequence of no "=" operator (std_typanalyze in analyze.c bails if there isn't one), so the planner has no idea about how wide 'val' actually is. I'm wondering if it is worth having at least an "=" operator to enable some minimal stats to be available for xml columns. regards Mark
[BUGS] psql: SELECT INTO with FETCH_COUNT enabled
While tinkering with some psql settings on 9.0beta1... [local]:5432|postgres=# \set FETCH_COUNT 1 [local]:5432|postgres=# SELECT foo INTO bar FROM baz; ERROR: DECLARE CURSOR cannot specify INTO LINE 2: SELECT foo INTO bar FROM baz; ^ [local]:5432|postgres=#! If I'm reading it right its using src/bin/psql/common.c's is_select_command() to determine if the query is cursor-able, and that function is just looking to see that the query starts with 'select' (or 'values'.) I'm not sure catching a non-alias use of INTO will be all that easy here without adding undue complexity. So considering no one else has reported it at least than I've been able to find, +1 for leaving it as is. Just thought I'd post it in case anyone has any better ideas for tackling it. - Josh -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
