Re: [BUGS] Lost search_path after transaction fails
Heikki Linnakangas wrote: ecpg implicitly runs everything inside transactions. You don't need to run START TRANSACTION, that's implicit. Therefore the set search_path command is in fact run in the same transaction as the failing insert, as also hinted by the warning there is already a transaction in progress at the START TRANSACTION command. Thanks for your reply. Committing after setting search_path does resolve this problem. It surprises me that a session parameter is treated in this way. -- 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 #4656: Indexes not used when comparing nextval() and currval() to integers
The following bug has been logged online:
Bug reference: 4656
Logged by: Mathias Seiler
Email address: mathias.sei...@gmail.com
PostgreSQL version: 8.3.6
Operating system: Debian Linux Lenny (testing)
Description:Indexes not used when comparing nextval() and currval()
to integers
Details:
Hello there
I'm not sure if I'm doing something terribly wrong here, but I when I
noticed a slowdown during a large transaction I dig into the problem and
found that when I use this prepared statement:
UPDATE booking_entries SET date = ? where id =
currval('booking_entries_id_seq'::regclass);
The index over the column id is not used. This obviously results in a full
table scan, which gets very slow after a few thousand entries.
So I tried to cast the returning value from currval() to integer (which is
the same type of id) but this still doesn't use the index (which is there):
EXPLAIN UPDATE booking_entries SET booking_date = now() where id =
nextval('booking_entries_id_seq'::regclass)::int4;
QUERY PLAN
-
Seq Scan on booking_entries (cost=0.00..351.95 rows=1 width=89)
Filter: (id = (nextval('booking_entries_id_seq'::regclass))::integer)
(2 rows)
set enable_seqscan = false;
SET
EXPLAIN UPDATE booking_entries SET booking_date = now() where id =
nextval('booking_entries_id_seq'::regclass)::int4;
QUERY PLAN
Seq Scan on booking_entries (cost=1.00..10163.01 rows=1
width=89)
Filter: (id = (nextval('booking_entries_id_seq'::regclass))::integer)
(2 rows)
EXPLAIN UPDATE booking_entries SET booking_date = now() where id = 1;
QUERY PLAN
-
Index Scan using booking_entries_pkey on booking_entries (cost=0.00..8.28
rows=1 width=89)
Index Cond: (id = 1)
(2 rows)
What's going wrong? Could this be a bug?
Kind Regards
P.S.
SELECT version();
version
PostgreSQL 8.3.6 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
4.3.3-3) 4.3.3
(1 row)
uname -s -r -v -m -o
Linux 2.6.26-1-686 #1 SMP Mon Dec 15 18:15:07 UTC 2008 i686 GNU/Linux
--
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 #4656: Indexes not used when comparing nextval() and currval() to integers
Mathias Seiler wrote:
I'm not sure if I'm doing something terribly wrong here, but I when I
noticed a slowdown during a large transaction I dig into the problem and
found that when I use this prepared statement:
UPDATE booking_entries SET date = ? where id =
currval('booking_entries_id_seq'::regclass);
The index over the column id is not used.
It's because currval and nextval are volatile functions
(http://www.postgresql.org/docs/8.3/interactive/xfunc-volatility.html).
Because of that, a lot of optimizations are disabled for them, and they
can not be used as index scan predicates. The interpretation of that
query is that you wanted to evaluate currval/nextval for all the rows in
the table, even though the UPDATE only matches some of the rows.
You can read the value returned by currval into a host language variable
and send it back as a ?.
Or you can create a wrapper function around currval that's marked as
stable instead of volatile, to hide currval's volatility. However, that
would amount to lying to the optimizer and you might get some surprising
results with more complex queries, so I wouldn't recommend it.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.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] BUG #4658: copy problem
The following bug has been logged online: Bug reference: 4658 Logged by: Brundle Fly Email address: mindqua...@gmail.com PostgreSQL version: 8.3 Operating system: Linux Description:copy problem Details: bdsimple=# copy (select * from customer where customer_id10) to '/tmp/clientes.dat' delimiter '|'; COPY 18 bdsimple=# copy (select * from customer where customer_id10) to '/tmp/clientes.dat' delimiter '|'; COPY 18 bdsimple=# This is ok in ubuntu, but in centos doesnt work?. -- 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 #4658: copy problem
Brundle Fly escreveu: This is ok in ubuntu, but in centos doesnt work?. COPY query TO is only available in = 8.2. Are you sure CentOS is using a 8.3 version too? If so, what is the error message? -- Euler Taveira de Oliveira http://www.timbira.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] BUG #4660: float functions return -0
ITAGAKI Takahiro itagaki.takah...@oss.ntt.co.jp writes: We avoid -0 in float8um (unary minus), I wonder why we do that. 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
