Re: [HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
Guillaume Smet ha scritto: > On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut wrote: >> The question is how you want to implement this in a data type independent >> fashion. You can't assume that increasing the typmod is a noop for all data >> types. > > Sure. See my previous answer on -hackers (I don't think this > discussion belong to -bugs) and especially the discussion in the > archives about Jonas' patch. I recently had a similar problem when I added some domains to the application. ALTER TABLE ... TYPE varchar_dom was leading to a full table rewrite even though the underlying type definition were exactly the same (i.e. varchar(64)). I can live with it, but I suppose this fix might be related to the varlen one. Cheers -- Matteo Beccati OpenX - http://www.openx.org -- 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 #5255: COUNT(*) returns wrong result with LEFT JOIN
The following bug has been logged online: Bug reference: 5255 Logged by: Matteo Beccati Email address: p...@beccati.com PostgreSQL version: 8.5alpha3 Operating system: NetBSD 5.0.1 Description:COUNT(*) returns wrong result with LEFT JOIN Details: Discovered this while fixing the php test suite to deal with 8.5 changes. With the following data set a SELECT * query returns 1 rows, while SELECT COUNT(*) returns 2. CREATE TABLE a (id int PRIMARY KEY); CREATE TABLE b (id int PRIMARY KEY, a_id int); INSERT INTO a VALUES (0), (1); INSERT INTO b VALUES (0, 0), (1, NULL); test=# SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); id | a_id | id +--+ 1 | | (1 row) test=# SELECT COUNT(*) FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); count --- 2 (1 row) -- 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 #5255: COUNT(*) returns wrong result with LEFT JOIN
Il 25/12/2009 18:13, Tom Lane ha scritto: I wrote: I guess we missed something about when it's safe to do this optimization. I've applied the attached patch to fix this. Thanks. Everything's working fine now! Merry Xmas -- Matteo Beccati Development & Consulting - http://www.beccati.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] Postgresql 9.1.2 - abnormal memory usage
Hi Heikki, On 12/12/2011 09:29, Heikki Linnakangas wrote: > On 12.12.2011 08:26, Tom Lane wrote: >> Andres Freund writes: >>> In 3b8161723c645853021b57330dd2ea0484ec6131 Heikki made DatumGetInetP >>> unpack >>> toasted values. Unfortunately the btree support functions for the >>> inet type >>> didn't free memory which they have to do in contrast to about >>> everything else. >> >>> I fixed a few more functions than strictly necessary but I guess >>> thats ok. >> >> Seems like the correct fix is to revert these functions to the former >> behavior, ie they should be using the PP macros not the unpacking ones. > > Agreed, there's no need to unpack here. Fixed, thanks for the report! Just to clarify, am I correct assuming that the issue does not affect tables which have non-indexed inet fields? Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.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 #6416: Expression index not used with UNION ALL queries
On 29/01/2012 16:06, p...@beccati.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6416
> Logged by: Matteo Beccati
> Email address: p...@beccati.com
> PostgreSQL version: 9.1.2
> Operating system: Debian Sqeeze
> Description:
>
> I've just noticed that an expression index I've created was not used with a
> view contiaining a UNION ALL. Switching to UNION or querying the table
> directly works as expected.
>
> A self contained test case follows:
Sorry about the formatting issue. You will find it attached.
Cheers
--
Matteo Beccati
Development & Consulting - http://www.beccati.com/
regression=# CREATE FUNCTION ab(in text, in text, out ab text) AS $$BEGIN ab :=
$1 || $2; END;$$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION
regression=# CREATE table t1 (a text, b text);
CREATE TABLE
regression=# CREATE INDEX t1_ab_idx on t1 (ab(a, b));
CREATE INDEX
regression=# CREATE table t2 (ab text primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for
table "t2"
CREATE TABLE
regression=# INSERT INTO t1 VALUES ('a', 'b');
INSERT 0 1
regression=# INSERT INTO t2 VALUES ('ab');
INSERT 0 1
regression=# VACUUM ANALYZE ;
VACUUM
regression=# SET enable_seqscan = false;
SET
regression=# EXPLAIN ANALYZE SELECT * FROM (SELECT ab(a, b) AS ab FROM t1 UNION
ALL SELECT * FROM t2) t WHERE ab = 'ab';
QUERY PLAN
-
Result (cost=100.00..109.53 rows=2 width=18) (actual
time=0.052..0.066 rows=2 loops=1)
-> Append (cost=100.00..109.53 rows=2 width=18) (actual
time=0.052..0.065 rows=2 loops=1)
-> Seq Scan on t1 (cost=100.00..101.26 rows=1
width=32) (actual time=0.051..0.052 rows=1 loops=1)
Filter: (ab(a, b) = 'ab'::text)
-> Index Scan using t2_pkey on t2 (cost=0.00..8.27 rows=1 width=3)
(actual time=0.010..0.011 rows=1 loops=1)
Index Cond: (ab = 'ab'::text)
Total runtime: 0.106 ms
(7 rows)
regression=# EXPLAIN ANALYZE SELECT * FROM (SELECT ab(a, b) AS ab FROM t1 UNION
SELECT * FROM t2) t WHERE ab = 'ab';
QUERY PLAN
Unique (cost=17.07..17.08 rows=2 width=4) (actual time=0.071..0.073 rows=1
loops=1)
-> Sort (cost=17.07..17.07 rows=2 width=4) (actual time=0.070..0.070
rows=2 loops=1)
Sort Key: (ab(t1.a, t1.b))
Sort Method: quicksort Memory: 25kB
-> Append (cost=0.25..17.06 rows=2 width=4) (actual
time=0.050..0.058 rows=2 loops=1)
-> Index Scan using t1_ab_idx on t1 (cost=0.25..8.77 rows=1
width=4) (actual time=0.049..0.050 rows=1 loops=1)
Index Cond: (ab(a, b) = 'ab'::text)
-> Index Scan using t2_pkey on t2 (cost=0.00..8.27 rows=1
width=3) (actual time=0.004..0.005 rows=1 loops=1)
Index Cond: (ab = 'ab'::text)
Total runtime: 0.116 ms
(10 rows)
regression=# EXPLAIN ANALYZE SELECT * FROM (SELECT ab(a, b) AS ab FROM t1) t
WHERE ab = 'ab';
QUERY PLAN
--
Index Scan using t1_ab_idx on t1 (cost=0.25..8.77 rows=1 width=4) (actual
time=0.030..0.032 rows=1 loops=1)
Index Cond: (ab(a, b) = 'ab'::text)
Total runtime: 0.048 ms
(3 rows)
--
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 #6416: Expression index not used with UNION ALL queries
On 29/01/2012 22:33, Tom Lane wrote: > Matteo Beccati writes: >>> I've just noticed that an expression index I've created was not used with a >>> view contiaining a UNION ALL. Switching to UNION or querying the table >>> directly works as expected. > > Looks like I broke this back in November :-(. Fixed, thanks for the > report. Thanks a lot Tom! Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.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] Errors on CREATE TABLE IF NOT EXISTS
Hi, I've recently seen a few errors on our continuous integration system during a test using a badly written partitioning trigger. The function was basically checking for the existence of the partition table at every insert and was running a CREATE TABLE IF NOT EXISTS statement in case it was needed. What baffled me was that the function was exiting with an ERROR, rather than succeeding with a NOTICE, e.g.: ERROR: relation "orders_2012_03" already exists CONTEXT: SQL statement "CREATE UNLOGGED TABLE IF NOT EXISTS history.orders_2012_03(CHECK (store_t_stamp >= '2012-03-01 00:00:00' AND store_t_stamp < '2012-04-01 00:00:00')) INHERITS (history.orders)" Since then I've made the partitioning functions a bit smarter and I'm also catching the exception just in case. I've tried to come up with a self-contained test case but I haven't been able to replicate the error above. However the following script performs a few concurrent CREATE TABLE IF NOT EXISTS statements that produce some unexpected errors (using 9.1.2). postgres@spritz:~$ cat crtest.sh #!/bin/sh for i in `seq 1 10`; do psql -c 'CREATE TABLE IF NOT EXISTS _foo (x int PRIMARY KEY)' 2>&1 & done sleep 2 psql -c 'DROP TABLE _foo' postgres@spritz:~$ ./crtest.sh NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "_foo_pkey" for table "_foo" CREATE TABLE ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. ERROR: duplicate key value violates unique constraint "pg_type_typname_nsp_index" DETAIL: Key (typname, typnamespace)=(_foo, 2200) already exists. DROP TABLE I'm not sure if the two failures are related in some way, but I thought it was good to report them both anyway. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.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] Errors on CREATE TABLE IF NOT EXISTS
Hi Robert, On 27/04/2012 20:24, Robert Haas wrote: The bottom line is that CREATE TABLE IF NOT EXISTS doesn't pretend to handle concurrency issues any better than regular old CREATE TABLE, which is to say not very well. Thanks for the thorough reply. I guessed it was something like that, but I thought it was better to report that anyway, just in case. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.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] Errors on CREATE TABLE IF NOT EXISTS
Hi Alvaro, On 01/05/2012 21:36, Alvaro Herrera wrote: The question you were asking has already been answered, but I think it's worth pointing out that a partitioned-insert trigger that has to check whether the partition exist beforehand is a lot slower than one that doesn't have to. Our usual suggestion is to create the partitions by some other means, e.g. create a couple months worth of weekly partitions, a couple of months ahead of time, via cron. The insert trigger is then assured that the partition exists, and it can become faster by not having to check. Thanks for the suggestion. I will surely follow the advice as soon as the load starts to grow. For now catching the "table not found" exception within the insert trigger and creating the table on the fly seems a good balance between performance and ease of use. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
