[BUGS] BUG #5479: PosgreSQL Documentation does not mention OPEN
The following bug has been logged online: Bug reference: 5479 Logged by: Kurt wagner Email address: kurt.wag...@leoni.com PostgreSQL version: 8.4 Operating system: HP-UX Description:PosgreSQL Documentation does not mention OPEN Details: When checking the Web Site http://www.postgresql.org/docs/8.4/static/sql-commands.html I recognized that the commands "declare" and "close" are documented, but I failed to find "open". Could you please add it sometime? -- 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 #5479: PosgreSQL Documentation does not mention OPEN
On 28/05/10 12:06, Kurt wagner wrote: When checking the Web Site http://www.postgresql.org/docs/8.4/static/sql-commands.html I recognized that the commands "declare" and "close" are documented, but I failed to find "open". OPEN is not an SQL command like DECLARE and CLOSE. It is part of PL/pgSQL and will only work within a PL/pgSQL function, it is documented at http://www.postgresql.org/docs/8.4/static/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING. Also note that PL/pgSQL's DECLARE and CLOSE are different from the SQL commands DECLARE and CLOSE. -- 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
Re: [BUGS] BUG #5476: sequence corruption
On 2010-05-27, Piergiorgio Buongiovanni wrote: > > The following bug has been logged online: > > Bug reference: 5476 > Logged by: Piergiorgio Buongiovanni > Email address: p.buongiova...@net-international.com > PostgreSQL version: 8.3.4 > Operating system: CentOS (Redhat 5.4) > Description:sequence corruption > Details: > > We experimented a sequence corruption on a table: > If we now look at the sequence data using pgAdmin version 1.8.4, we see the > following: > > CREATE SEQUENCE business.subject_isid_seq > INCREMENT 1 > MINVALUE 1 > MAXVALUE 9223372036854775807 > START 58827944 > CACHE 1; > ALTER TABLE business.subject_isid_seq OWNER TO netdw_owner; > > The problem is that now the search on the table business.Subject (where the > sequence is used) performs very slowly. It seems the data are corrupted but > at the end I can see that there is a record created with the number 463663 > instead of 57. As you can see the next value will be used is 58827944. > Which is the problem? Can I recover this situation and restore the right > sequence value? I tried to set the START value to 58 but, as you can see, > the value changed. have you tried vacuum full on the table? I get the feeling that it may have several million deleted rows. VACUUM FULL subject; -- 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 #5480: Autovacuum interferes with operations (e.g. truncate) on very large databases
The following bug has been logged online: Bug reference: 5480 Logged by: Sakari Maaranen Email address: s...@iki.fi PostgreSQL version: 8.4.4 Operating system: CentOS 5.5 Description:Autovacuum interferes with operations (e.g. truncate) on very large databases Details: I'm using PostgreSQL to maintain a very large database that holds the full OpenStreetMap database. For example, the table for geospatial nodes (points on the map) contains hundreds of millions of rows. The documentation says that the best way to use autovacuum is to let the database vacuum often. However when the tables are very large, this can take a very long time. The documentation also says that the quickest way to empty a whole table would be by using the truncate command. However, if the autovacuum daemon happens to be working on the same table, it causes the truncate command to hang for a very long time to wait the vacuum to finish. This makes no sense, because the table is going to be emptied, so there's no point in vacuuming it first (or is there?) For some reason PostgreSQL failed when I had two separate processes working on different tables of the same very large database: 1. I was restoring one table via psql from a pg_dump that was created earlier in the default (COPY) mode. 2. At the same time there was another process inserting data in another table via a JDBC connection. Both tables are in the same database. Both processes were long-running operations -- (1) the COPY restoration taking several hours and (2) the JDBC connection running several days. At some point the database failed for an unknown reason. This is the output from the restoration process: bash-3.2$ 7za x -so /var/lib/pgsql/backups/osm_way_nodes.pgdump.7z | pv | psql -d routing 7-Zip (A) 4.61 beta Copyright (c) 1999-2008 Igor Pavlov 2008-11-23 p7zip Version 4.61 (locale=en_US.UTF-8,Utf16=on,HugeFiles=on,8 CPUs) Processing archive: /var/lib/pgsql/backups/osm_way_nodes.pgdump.7z Extracting osm_way_nodes.pgdump SET SET SET SET <...running for several hours here, then suddenly...> WARNING: terminating connection because of crash of another server process ] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. CONTEXT: COPY osm_way_nodes, line 161870753: "17043232 176883291 356" server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost The JDBC connection had been running for over a week before, then restarted about 3h 12min 50sec before this crash happened. This is the output from the JDBC connection at the time of crash: 12:27 osm2postgis.core.Monitor run INFO: Time elapsed 0 d 03:12:50; Committed up to line 718713520; 12:27 osm2postgis.core.Monitor run INFO: Cumulative: public.osm_changesets:ignored=3607228 public.osm_nodes:created=3996789 public.osm_nodes:ig nored=493155015 public.osm_nodes:verified=4050 12:48 postgis.dao.OutputTarget keepAlive WARNING: The database connection failed. 12:48 postgis.dao.OutputTarget keepAlive FINE: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend. Exception in thread "Monitor" java.lang.IllegalStateException: Database connection died. at net.sourceforge.osm2postgis.core.Monitor.run(Monitor.java:149) at net.sourceforge.osm2postgis.Shell.main(Shell.java:207) 12:48 postgis.dao.OutputTarget rollback WARNING: Rollback failed. org.postgresql.util.PSQLException: An I/O error occured while sending to the backend. at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283) at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(Abstr actJdbc2Connection.java:671) at org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(AbstractJdbc2Connectio n.java:708) at net.sourceforge.osm2postgis.dao.OutputTarget.rollback(OutputTarget.java:335) at net.sourceforge.osm2postgis.dao.OutputTarget.write(OutputTarget.java:311) at net.sourceforge.osm2postgis.dao.OutputTarget.retryWrite(OutputTarget.java:35 8) at net.sourceforge.osm2postgis.core.PostGISWriter.run(PostGISWriter.java:91) at java.lang.Thread.run(Unknown Source) Caused by: java.io.IOException: Stream closed at sun.nio.cs.StreamEncoder.ensureOpen(Unknown Source) at sun.nio.cs.StreamEncoder.flush(Unknown Source) at java.io.OutputStreamWriter.flush(Unknown Source) at org.postgresql.core.PGStream.flush(PGStream.java:507) at org.postgresql.core.v3.QueryExecutorImpl.sendSync(QueryExecutorImpl.java:110 7) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:256) ... 7 m
Re: [BUGS] Query causing explosion of temp space with join involving partitioning
I made a brute force check and indeed, for one of the parameters the query was switching to sequential scans (or bitmaps scans with condition on survey_pk=16 only if sequential scans were off). After closer look at the plan cardinalities I thought it would be worthy to increase histogram size and I set statistics on sources(srcid) to 1000 from default 10. It fixed the plan! Sources table was around 100M so skewness in this range must have been looking odd for the planner.. Thank you for the hints! Best Regards, Krzysztof On May 27, 2010, at 6:41 PM, Tom Lane wrote: > Krzysztof Nienartowicz writes: >> Logs of the system running queries are not utterly clear, so chasing the >> parameters for the explosive query is not that simple (shared logs between >> multiple threads), but from what I see there is no difference between them >> and the plan looks like (without removal of irrelevant parameters this time, >> most of them are float8, but also bytea) >> [ nestloop with inner index scans over the inherited table ] > > Well, that type of plan isn't going to consume much memory or disk > space. What I suspect is happening is that sometimes, depending on the > specific parameter values called out in the query, the planner is > switching to another plan type that does consume lots of space (probably > via sort or hash temp files). The most obvious guess is that that will > happen when the range limits on srcid get far enough apart to make a > nestloop not look cheap. You could try experimenting with EXPLAIN and > different constant values to see what you get. > > 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 #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery
The following bug has been logged online: Bug reference: 5477 Logged by: Matt Nourse Email address: matt...@nplus1.com.au PostgreSQL version: 8.4 Operating system: Linux (Debian and Red Hat) Description:CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery Details: Set up: CREATE DOMAIN test_id_domain INT NOT NULL; CREATE TABLE test_state(id test_id_domain PRIMARY KEY, display_value varchar(20) NOT NULL); CREATE TABLE test_city(state_id test_id_domain REFERENCES test_state(id)); This produces an error as expected: INSERT INTO test_city(state_id) VALUES (NULL); This successfully inserts a NULL value into the state_id field: INSERT INTO test_city(state_id) VALUES ((SELECT id FROM test_state WHERE display_value = 'Nonexistent state')); When I use simpler subqueries (eg SELECT 1 WHERE 1 = 0), the NOT NULL constraint is enforced. If I remove the test_id_domain domain and replace its use with INT NOT NULL, the constraint is enforced. Thanks and regards, Matt -- 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 #5478: ILIKE operator returns wrong result
The following bug has been logged online: Bug reference: 5478 Logged by: Markus Email address: markus.her...@outpost24.com PostgreSQL version: PostgreSQL 8.4. Operating system: Ubuntu 10.04 Description:ILIKE operator returns wrong result Details: The following query select 'ba' ilike '%__%'; return true as expected in 8.2 but false in 8.4. -- 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 #5478: ILIKE operator returns wrong result
Markus wrote: > > The following bug has been logged online: > > Bug reference: 5478 > Logged by: Markus > Email address: markus.her...@outpost24.com > PostgreSQL version: PostgreSQL 8.4. > Operating system: Ubuntu 10.04 > Description:ILIKE operator returns wrong result > Details: > > The following query > > select 'ba' ilike '%__%'; > > return true as expected in 8.2 but false in 8.4. I can confirm the odd behavior in current CVS: test=> select 'ba' ilike '%__%'; ?column? -- f (1 row) test=> select 'ba' like '__'; ?column? -- t (1 row) test=> select 'ba' like '__%'; ?column? -- t (1 row) test=> select 'ba' like '%_%'; ?column? -- t (1 row) It seems to be the leading '%' it does not like. Our docs clearly state your syntax is recommended: LIKE pattern matching always covers the entire string. Therefore, to match a sequence anywhere within a string, the pattern must start and end with a percent sign. -- Bruce Momjian http://momjian.us EnterpriseDB http://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
Re: [BUGS] BUG #5480: Autovacuum interferes with operations (e.g. truncate) on very large databases
"Sakari Maaranen" writes: > The documentation also says that the quickest way to empty a whole table > would be by using the truncate command. However, if the autovacuum daemon > happens to be working on the same table, it causes the truncate command to > hang for a very long time to wait the vacuum to finish. That is not supposed to happen: other commands attempting to access the table should kick the autovacuum off it (ie, force cancellation of the autovacuum operation). Now it is possible that there's some path of control wherein vacuum fails to check for a cancel interrupt for a very long time. That seems possible if you are using an uncommon index type or (less likely) an unusual data type. What's the schema of the problem table exactly? > For some reason PostgreSQL failed when I had two separate processes working > on different tables of the same very large database: > 1. I was restoring one table via psql from a pg_dump that was created > earlier in the default (COPY) mode. > 2. At the same time there was another process inserting data in another > table via a JDBC connection. Well, this also opens the possibility that what was blocking the truncate was not autovacuum at all, but some lock held by one of the other active sessions. An open transaction that has even just selected from a table will block truncate, since that needs an exclusive lock. > WARNING: terminating connection because of crash of another server process > ] That is definitely an indication of a problem, but you've completely failed to provide any useful data about it, only client-side reports from sessions that weren't the one that crashed. You might try looking in the postmaster log to see if there is anything relevant recorded there. If there's a core dump file, a stack trace from that would be even better. > Please either make PostgreSQL work with very large databases, or give > recommendations on the maximum size of tables that should work well with > PostgreSQL autovacuum and all. Thank you! There is really not much we can do with such a vague problem report. If you can provide details sufficient to let someone else reproduce the misbehavior, we'll certainly look into it. 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 #5478: ILIKE operator returns wrong result
"Markus" writes: > select 'ba' ilike '%__%'; > return true as expected in 8.2 but false in 8.4. I have a feeling that this represents still another bug in the special-case path for % followed by _ (cf bug #4821). If so, maybe we ought to just toss out that optimization? 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 #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery
"Matt Nourse" writes: > CREATE DOMAIN test_id_domain INT NOT NULL; > CREATE TABLE test_state(id test_id_domain PRIMARY KEY, display_value > varchar(20) NOT NULL); > CREATE TABLE test_city(state_id test_id_domain REFERENCES test_state(id)); > This produces an error as expected: > INSERT INTO test_city(state_id) VALUES (NULL); > This successfully inserts a NULL value into the state_id field: > INSERT INTO test_city(state_id) VALUES ((SELECT id FROM test_state WHERE > display_value = 'Nonexistent state')); There are any number of ways you can get a similar result, for example a LEFT JOIN. To my mind, this demonstrates why not-null constraints associated with datatypes are a fundamentally flawed concept. If the SELECT or LEFT JOIN can produce a null value, as it clearly can, then it's nonsensical to think that the output column should be considered to be of a NOT NULL domain type. But what else should it be? If we smash domains to their base types when assigning result types of queries, that will make many people unhappy. Moral: NOT NULL constraints at the domain level suck. Don't use 'em. 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 #5478: ILIKE operator returns wrong result
Tom Lane wrote:
> "Markus" writes:
> > select 'ba' ilike '%__%';
> > return true as expected in 8.2 but false in 8.4.
>
> I have a feeling that this represents still another bug in the
> special-case path for % followed by _ (cf bug #4821). If so,
> maybe we ought to just toss out that optimization?
Yea, looks like it is this code in like_match.c:
/* %_ is the same as _% - avoid matching _ repeatedly */
do
{
NextChar(t, tlen);
NextByte(p, plen);
} while (tlen > 0 && plen > 0 && *p == '_');
--
Bruce Momjian http://momjian.us
EnterpriseDB http://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
Re: [BUGS] BUG #5478: ILIKE operator returns wrong result
Bruce Momjian writes:
> Tom Lane wrote:
>> I have a feeling that this represents still another bug in the
>> special-case path for % followed by _ (cf bug #4821). If so,
>> maybe we ought to just toss out that optimization?
> Yea, looks like it is this code in like_match.c:
No, actually it's the bit right after that:
/* Look for a place that matches the rest of the pattern */
while (tlen > 0)
{
intmatched = MatchText(t, tlen, p, plen);
if (matched != LIKE_FALSE)
return matched; /* TRUE or ABORT */
NextChar(t, tlen);
}
If tlen == 0 when we reach this loop, we'll fall through and fail.
But that is wrong since we need to consider the possibility that
the remaining pattern can match a zero-length substring. So the
loop needs to be changed to attempt a recursive MatchText for
tlen equal to zero as well as greater than zero.
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 #5478: ILIKE operator returns wrong result
Tom Lane wrote:
> Bruce Momjian writes:
> > Tom Lane wrote:
> >> I have a feeling that this represents still another bug in the
> >> special-case path for % followed by _ (cf bug #4821). If so,
> >> maybe we ought to just toss out that optimization?
>
> > Yea, looks like it is this code in like_match.c:
>
> No, actually it's the bit right after that:
>
> /* Look for a place that matches the rest of the pattern */
> while (tlen > 0)
> {
> intmatched = MatchText(t, tlen, p, plen);
>
> if (matched != LIKE_FALSE)
> return matched; /* TRUE or ABORT */
>
> NextChar(t, tlen);
> }
>
> If tlen == 0 when we reach this loop, we'll fall through and fail.
> But that is wrong since we need to consider the possibility that
> the remaining pattern can match a zero-length substring. So the
> loop needs to be changed to attempt a recursive MatchText for
> tlen equal to zero as well as greater than zero.
I took a different approach. I think the problem is that we check for
end of pattern without consuming '%' patterns. I copied that consume
loop from code above that where we also test for end of pattern.
With the attached patch (which includes a regression test addition), it
works fine:
test=> select 'ba' like '%__%';
?column?
--
t
(1 row)
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
Index: src/backend/utils/adt/like_match.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/like_match.c,v
retrieving revision 1.27
diff -c -c -r1.27 like_match.c
*** src/backend/utils/adt/like_match.c 2 Jan 2010 16:57:54 - 1.27
--- src/backend/utils/adt/like_match.c 28 May 2010 15:36:09 -
***
*** 139,144
--- 139,146
* n _'s matches any string of at least n
characters, and we
* have now found there are at least n
characters.
*/
+ while (plen > 0 && *p == '%')
+ NextByte(p, plen);
if (plen <= 0)
return LIKE_TRUE;
Index: src/test/regress/expected/strings.out
===
RCS file: /cvsroot/pgsql/src/test/regress/expected/strings.out,v
retrieving revision 1.40
diff -c -c -r1.40 strings.out
*** src/test/regress/expected/strings.out 25 Jan 2010 20:55:32 -
1.40
--- src/test/regress/expected/strings.out 28 May 2010 15:36:12 -
***
*** 943,948
--- 943,954
t
(1 row)
+ SELECT 'jack' LIKE '%%' AS "true";
+ true
+ --
+ t
+ (1 row)
+
--
-- test ILIKE (case-insensitive LIKE)
-- Be sure to form every test as an ILIKE/NOT ILIKE pair.
Index: src/test/regress/sql/strings.sql
===
RCS file: /cvsroot/pgsql/src/test/regress/sql/strings.sql,v
retrieving revision 1.28
diff -c -c -r1.28 strings.sql
*** src/test/regress/sql/strings.sql25 Jan 2010 20:55:32 - 1.28
--- src/test/regress/sql/strings.sql28 May 2010 15:36:12 -
***
*** 282,287
--- 282,288
SELECT 'be_r' LIKE '__e__r' ESCAPE '_' AS "false";
SELECT 'be_r' NOT LIKE '__e__r' ESCAPE '_' AS "true";
+ SELECT 'jack' LIKE '%%' AS "true";
--
-- test ILIKE (case-insensitive LIKE)
***
*** 310,316
SELECT 'foo' LIKE '__%' as t, 'foo' LIKE '___%' as t, 'foo' LIKE '%' as f;
SELECT 'foo' LIKE '%__' as t, 'foo' LIKE '%___' as t, 'foo' LIKE '%' as f;
-
--
-- test implicit type conversion
--
--- 311,316
--
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 #5478: ILIKE operator returns wrong result
Bruce Momjian writes: > Tom Lane wrote: >> If tlen == 0 when we reach this loop, we'll fall through and fail. >> But that is wrong since we need to consider the possibility that >> the remaining pattern can match a zero-length substring. So the >> loop needs to be changed to attempt a recursive MatchText for >> tlen equal to zero as well as greater than zero. > I took a different approach. I think the problem is that we check for > end of pattern without consuming '%' patterns. I copied that consume > loop from code above that where we also test for end of pattern. > With the attached patch (which includes a regression test addition), it > works fine: No, that patch is just plain wrong. It eats %'s that would affect the later recursive MatchText calls. 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 #5478: ILIKE operator returns wrong result
I wrote: > No, that patch is just plain wrong. It eats %'s that would affect > the later recursive MatchText calls. Hmm ... actually, it's not wrong, but it's not good either. What we really ought to do here is not just eat _'s following %, but eat *any mixture of* % and _, advancing over a text character per _. The subsequent search loop is reached only when we find a literal pattern character to match. This generalizes the original intention of not using recursion to deal with simple advancing. 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 #5478: ILIKE operator returns wrong result
BTW, while I'm looking at this, I notice that there was an oversight in the change that made us throw an error for \ at the end of the LIKE pattern. We throw error in the first code chunk that deals with \ but we don't do so here: if (plen < 2) return LIKE_FALSE; firstpat = CHAR(p[1]); In some cases the problem is masked because we'll eventually apply the normal \ processing, but I think there are other cases where we'll reach a LIKE_ABORT condition and return false without ever throwing the error. Seems like this should be fixed. But should we back-patch that fix into 8.4? We didn't backpatch the original change for fear of breaking existing apps, and the same argument could probably be made this time. Should I change it in 8.4, or only 9.0? 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 #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery
Tom Lane wrote: > "Matt Nourse" writes: > > CREATE DOMAIN test_id_domain INT NOT NULL; > > CREATE TABLE test_state(id test_id_domain PRIMARY KEY, display_value > > varchar(20) NOT NULL); > > CREATE TABLE test_city(state_id test_id_domain REFERENCES test_state(id)); > > > This produces an error as expected: > > > INSERT INTO test_city(state_id) VALUES (NULL); > > > This successfully inserts a NULL value into the state_id field: > > > INSERT INTO test_city(state_id) VALUES ((SELECT id FROM test_state WHERE > > display_value = 'Nonexistent state')); > > There are any number of ways you can get a similar result, for example > a LEFT JOIN. To my mind, this demonstrates why not-null constraints > associated with datatypes are a fundamentally flawed concept. If the > SELECT or LEFT JOIN can produce a null value, as it clearly can, then > it's nonsensical to think that the output column should be considered > to be of a NOT NULL domain type. But what else should it be? If we > smash domains to their base types when assigning result types of > queries, that will make many people unhappy. > > Moral: NOT NULL constraints at the domain level suck. Don't use 'em. I have written up the following documentation patch to document this behavior. It doesn't seem like something we want to fix, so I am not making it a TODO item. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com Index: doc/src/sgml/ref/create_domain.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_domain.sgml,v retrieving revision 1.34 diff -c -c -r1.34 create_domain.sgml *** doc/src/sgml/ref/create_domain.sgml 3 Apr 2010 07:22:58 - 1.34 --- doc/src/sgml/ref/create_domain.sgml 28 May 2010 17:19:35 - *** *** 121,127 NOT NULL ! Values of this domain are not allowed to be null. --- 121,132 NOT NULL ! Values of this domain are normally prevented from being null. ! It is still possible for a domain with this constraint ! to take a null value if it is assigned a matching domain type ! that has become null, e.g. via a LEFT OUTER JOIN, or ! INSERT INTO tab (domcol) VALUES ((SELECT domcol FROM ! tab WHERE false)). -- 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 #5478: ILIKE operator returns wrong result
Tom Lane wrote: > BTW, while I'm looking at this, I notice that there was an oversight in > the change that made us throw an error for \ at the end of the LIKE > pattern. We throw error in the first code chunk that deals with \ > but we don't do so here: > > if (plen < 2) > return LIKE_FALSE; > firstpat = CHAR(p[1]); > > In some cases the problem is masked because we'll eventually apply the > normal \ processing, but I think there are other cases where we'll reach > a LIKE_ABORT condition and return false without ever throwing the error. > Seems like this should be fixed. But should we back-patch that fix into > 8.4? We didn't backpatch the original change for fear of breaking > existing apps, and the same argument could probably be made this time. > Should I change it in 8.4, or only 9.0? Tom has patch this and the fix will appear in the next minor release of Postgres 8.3.X and 8.4.X. -- Bruce Momjian http://momjian.us EnterpriseDB http://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
Re: [BUGS] psql: SELECT INTO with FETCH_COUNT enabled
I have added the following TODO: Fix FETCH_COUNT to handle SELECT ... INTO and WITH queries * http://archives.postgresql.org/pgsql-hackers/2010-05/msg01565.php * http://archives.postgresql.org/pgsql-bugs/2010-05/msg00192.php --- Josh Williams wrote: > 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 -- Bruce Momjian http://momjian.us EnterpriseDB http://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
Re: [BUGS] psql or pgbouncer bug?
Added to TODO:
Prevent psql from sending remaining single-line multi-statement queries
after reconnection
* http://archives.postgresql.org/pgsql-bugs/2010-05/msg00159.php
* http://archives.postgresql.org/pgsql-hackers/2010-05/msg01283.php
---
Tom Molesworth wrote:
> 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
--
Bruce Momjian http://momjian.us
EnterpriseDB http://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
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
Daniele Varrazzo wrote: > "If there is no match to the pattern, the function returns no rows" is > easily overlooked as "it returns null", or some other behaviour that > don't change the returned set. The point is, because the function is > listed in the string function, you would expect the function to > manipulate text, not the dataset. The function as it is is not safe to > be used in a construct > > SELECT foo, bar, regexp_matches(bar, pattern) FROM table; > > unless you really wanted: > > SELECT foo, bar, regexp_matches(bar, pattern) FROM table WHERE bar > ~ pattern; > > otherwise you have to take measures to be able to deal with records in > which the pattern is not matched, for example: > > SELECT foo, bar, regexp_matches(bar, pattern || '|') FROM table; > > the latter still doesn't work when bar is NULL: in this case the > record is dropped anyway, so I don't think it can be proposed as > general solution. > > The characteristics of returning a set of text[] is useful when the > user wants all the matches, not only the first one: the behaviour is > selected specifying the flag 'g' as third argument. > > >From this point of view, I hope it can be stated that in its current > form the regexp_matches() has not the most optimal interface. Please > accept my apology for the tone being too rude in my previous message. I found the description in the documentation quite confusing also. I have created the attached documention patch which is clearer about the behavior of regexp_matches(). -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.513 diff -c -c -r1.513 func.sgml *** doc/src/sgml/func.sgml 7 Apr 2010 06:12:52 - 1.513 --- doc/src/sgml/func.sgml 29 May 2010 01:59:32 - *** *** 3450,3463 It has the syntax regexp_matches(string, pattern , flags ). ! If there is no match to the pattern, the function returns ! no rows. If there is a match, the function returns a text array whose n'th element is the substring matching the n'th parenthesized subexpression of the pattern (not counting non-capturing parentheses; see below for ! details). If the pattern does not contain any parenthesized ! subexpressions, then the result is a single-element text array containing ! the substring matching the whole pattern. The flags parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag g causes the function to find --- 3450,3466 It has the syntax regexp_matches(string, pattern , flags ). ! The function can return no rows, one row, or multiple rows (see ! the g flag below). If the pattern ! does not match, the function returns no rows. If the pattern ! contains no parenthesized subexpressions, then each row ! returned is a single-element text array containing the substring ! matching the whole pattern. If the pattern contains parenthesized ! subexpressions, the function returns a text array whose n'th element is the substring matching the n'th parenthesized subexpression of the pattern (not counting non-capturing parentheses; see below for ! details). The flags parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag g causes the function to find -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
