Re: [BUGS] BUG #4618: nolock changes first column name of query result set to 'nolock'
On Fri, Jan 16, 2009 at 01:22:48PM +0100, Magnus Hagander wrote: brian wrote: query1 works fine, first column in table is called movieid1: select * from netflix.ratings where movieid = 1 query2 is a bug and should work just fine, but gives error: ERROR: column movieid does not exist LINE 2: select * from netflix.ratings with (nolock) where movieid = ... I don't know where you got with (nolock) from, but that's not PostgreSQL syntax - and AFAIK it's not standard SQL at all. Just remove it and you should be fine. with (nolock) is a SQL Server-ism for improving performance by using dirty reads. Thanks to MVCC, PostgreSQL doesn't need such nonsense. The second query created a table alias for netflix.ratings named with, whose first column (presumably movieid) is aliased as nolock. The following query probably would have worked (not that I'm recommending it): select * from netflix.ratings with (nolock) where nolock = ... -- Michael Fuhr -- 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 #4257: about unicode extend
On Sat, Jun 21, 2008 at 01:25:15PM +, arli weng wrote: PostgreSQL version: 8.3 What does SELECT version() return? I'm wondering if the server isn't 8.3 but rather an earlier version (see below). the command (chinese by utf-8): INSERT INTO title VALUES(46307243,46307898,'酋鼠ꕨ'); in sqlite text type, no problem.. in postgres report error: invalid byte sequence for encoding UNICODE: 0xf0 Your INSERT statement works for me in 8.3.3, 8.2.9, and 8.1.13. According to the release notes version 8.1 changed UNICODE to UTF8 and added support for 4-byte characters, so the fact that the error says UNICODE and your database doesn't appear to support 4-byte characters makes me wonder if you're running 8.0 or earlier. -- Michael Fuhr -- 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 #3965: UNIQUE constraint fails on long column values
On Wed, Feb 20, 2008 at 12:21:03PM +0100, Francisco Olarte Sanz wrote: On Wednesday 20 February 2008, Gregory Stark wrote: Unless you need cryptographic security I would not suggest using MD5. MD5 is intentionally designed to take a substantial amount of CPU resources to calculate. I thought it was the exact opposite, quoting from RFC1321: And if you *do* need cryptographic security then don't use MD5, and consider using SHA-256 instead of SHA-1. See RFC 4270 for discussion. ftp://ftp.rfc-editor.org/in-notes/rfc4270.txt -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #3935: about Unicode values
On Wed, Feb 06, 2008 at 06:11:24AM +, Vaibhav More wrote:
PostgreSQL version: 1.6.2
This looks like a pgAdmin version, not a PostgreSQL version. What
does SELECT version() return?
I have problem in displaying Unicode(UTF-8) integer values in my query.
insert in table values('२६','वैभव');
here २६ is an int value.
Could you expand on what problem you're having? What exactly are
you doing, what are you expecting to happen, and what actually does
happen?
Your int value appears to be 26 in Devanagari. Are you trying to
store that value in an integer column? I don't think PostgreSQL
supports numbers in non-Latin scripts unless your underlying strtol()
and sprintf() functions do.
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] BUG #3395: varchar[] -- Warning vendor specific
On Tue, Jun 19, 2007 at 09:43:14AM +, Murali Doss wrote: Bug reference: 3395 Logged by: Murali Doss Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: Window XP Description:varchar[] -- Warning vendor specific Details: In postgresql ,i am using varchar[] array which is giving me warning as vendor specific.Please let me know any alternate to be used instead of varchar[]. How are you using varchar[] and what's the exact text of the warning? Please provide a set of steps that somebody could perform to see the same thing you're seeing. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #3394: Partial search not working
On Tue, Jun 19, 2007 at 02:55:07PM +0530, Murali Doss wrote: No result but the matching data is available in table What values are not matching that you expect to match? Can you provide a reproducible test case? What are your locale settings and encoding? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3269: PSQL does not display error output
On Fri, May 11, 2007 at 01:06:02PM +, Bojan Jovanovic wrote: We just upgraded to 8.2.4, and noticed that psql does not display error messages, e.g.: [...] shp_production=# select * from asdfafsdf; shp_production=# commit; ROLLBACK What's the output of show client_min_messages? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] alter column type (from timestamp to date) violates not null
On Fri, Mar 09, 2007 at 04:55:59AM -0800, psmith wrote: When change a column type from timestamp to date, the 'infinity' and '- infinity' values will be NULL. Even if the column has a not null constraint. [...] version -- PostgreSQL 8.1.0 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20051023 (prerelease) (Debian 4.0.2-3) (1 sor) This appears to be fixed already: test= alter table a alter t type date; ERROR: column t contains null values test= select version(); version --- PostgreSQL 8.1.8 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2 (1 row) -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2975: UNIQUE INDEX doesn't work
On Wed, Feb 14, 2007 at 10:01:58AM +0100, Steven Lambert wrote: You are right, the index has more columns and one of them have most of the time the value NULL. It is the date when the record has been deleted See Unique Constraints in the documentation, in particular the last paragraph: http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html#AEN2016 However, null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This has been discussed before; search the archives for words like null, unique, and comparison or compare. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2975: UNIQUE INDEX doesn't work
On Thu, Feb 08, 2007 at 11:57:19AM -0500, Bruce Momjian wrote: Steven wrote: I have a unique index on a table, but it is still possible to insert a duplicated row, without any message what so ever. Please show us a self-contained example. Are you perchance inserting NULL into the column(s) with the unique index? -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2895: Private Use Unicode character crashes server when using ILIKE
On Tue, Jan 23, 2007 at 09:09:52PM -0500, Tom Lane wrote: James Russell [EMAIL PROTECTED] writes: If a text field contains a such a character, and if the search term contains certain Unicode characters, then queries using ILIKE will crash the server. I can't reproduce this with your example, so I suppose there's some critical bit of information you've left out. Can you provide a stack trace from the crash? This might be platform-dependent -- I get a crash with an 8.1.6 UTF-8 database on RHEL AS 4 x86_64 but not on Solaris 9/sparc 32-bit. I don't get a crash on either platform with 8.2.1 or 8.3devel. The crash seems to happen only with code points that have UTF-8 byte sequences of more than three bytes (U+1 and higher). Here's an example that segfaults in 8.1.6 on the Linux box: select e'\360\220\200\200' ilike e'%\342\204\242'; #0 MBMatchTextIC (t=0x8ebcbc �\220\200\200�7\212, tlen=4, p=0x0, plen=3) at like_match.c:195 195 if (ICHAREQ(t, p) || (*p == '\\') || (*p == '_')) (gdb) bt #0 MBMatchTextIC (t=0x8ebcbc �\220\200\200�7\212, tlen=4, p=0x0, plen=3) at like_match.c:195 #1 0x005ae558 in texticlike (fcinfo=Variable fcinfo is not available. ) at like.c:355 #2 0x00501044 in ExecMakeFunctionResult (fcache=0x90b530, econtext=0x90b400, isNull=0x90bdb0 , isDone=0x90bdd0) at execQual.c:1095 #3 0x00504c23 in ExecProject (projInfo=0x90bbf0, isDone=0x7fbfffdd74) at execQual.c:3704 #4 0x0050f64a in ExecResult (node=0x90b2e8) at nodeResult.c:157 #5 0x004ff90d in ExecProcNode (node=0x90b2e8) at execProcnode.c:306 #6 0x004feb8a in ExecutorRun (queryDesc=Variable queryDesc is not available. ) at execMain.c:1122 #7 0x0058857e in PortalRunSelect (portal=0x908ae8, forward=Variable forward is not available. ) at pquery.c:794 #8 0x00588b7f in PortalRun (portal=0x908ae8, count=9223372036854775807, dest=0x8ec510, altdest=0x8ec510, completionTag=0x7fbfffe0f0 ) at pquery.c:646 #9 0x00584846 in exec_simple_query (query_string=0x8eb4e8 select e'\\360\\220\\200\\200' ilike e'%\\342\\204\\242';) at postgres.c:1004 #10 0x005864ee in PostgresMain (argc=4, argv=0x8844a8, username=0x884390 mfuhr) at postgres.c:3232 #11 0x0055c31a in ServerLoop () at postmaster.c:2863 #12 0x0055d90a in PostmasterMain (argc=5, argv=0x8828c0) at postmaster.c:941 #13 0x0051d5e3 in main (argc=5, argv=0x8828c0) at main.c:265 -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2895: Private Use Unicode character crashes server when using ILIKE
On Tue, Jan 16, 2007 at 06:16:22AM +, James Russell wrote: Description: Private Use Unicode character crashes server when using ILIKE The archives show that ILIKE is known to be broken with multibyte characters in 8.1 and earlier, although I don't recall seeing reports of a crash resulting. I got a crash in 8.1.6 built from the latest source in CVS; here's a partial stack trace: (gdb) bt #0 MBMatchTextIC (t=0x2a98613d1c �\200\202\206, tlen=4, p=0x0, plen=4) at like_match.c:195 #1 0x005ae558 in texticlike (fcinfo=Variable fcinfo is not available. ) at like.c:355 I wonder if this is a problem only with code points outside of Plane 0, viz., those with UTF-8 sequences longer than three bytes. I don't get a crash with U+FFFD (E'\357\277\275') but I do with U+1 (E'\360\220\200\200') and other four-byte sequences. - I have not yet tried to reproduce the bug on the latest Postgres 8.2.x It appears to work in 8.2.1; at least it didn't crash. The 8.2 Release Notes contain the following item: * Allow ILIKE to work for multi-byte encodings (Tom) Internally, ILIKE now calls lower() and then uses LIKE. Locale-specific regular expression patterns still do not work in these encodings. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] 8.1.2 -32768::smallint
On Wed, Dec 13, 2006 at 03:03:43PM -, Jean-Gérard Pailloncy wrote: On PostgreSQL 8.1.2 select -32768::smallint throws the error ERROR: smallint out of range I think the cast is binding tighter than the unary minus, so the above is equivalent to select -(32768::smallint) which is why you're getting smallint out of range. This should work: select (-32768)::smallint -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] 8.1.2 -32768::smallint
On Wed, Dec 13, 2006 at 08:34:38AM -0700, Michael Fuhr wrote: On Wed, Dec 13, 2006 at 03:03:43PM -, Jean-Gérard Pailloncy wrote: On PostgreSQL 8.1.2 select -32768::smallint throws the error ERROR: smallint out of range I think the cast is binding tighter than the unary minus, Indeed it is; see the Operator Precedence table: http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-PRECEDENCE -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2791: Error while commiting
On Tue, Nov 28, 2006 at 02:13:15PM +, Raman Dabhade wrote: ERROR: SPI_execute_plan failed executing query commit: SPI_ERROR_TRANSACTION CONTEXT: PL/pgSQL function processrecords line 4 at SQL statement http://www.postgresql.org/docs/8.1/interactive/plpgsql-structure.html Functions and trigger procedures are always executed within a transaction established by an outer query -- they cannot start or commit that transaction, since there would be no context for them to execute in. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2631: database locking problem
On Sun, Sep 17, 2006 at 01:29:33AM -0400, Tom Lane wrote: Ross Elliott [EMAIL PROTECTED] writes: So, what happened between 8.1.3 and 8.1.4 that may have affected locking? Nothing that I know of. Please provide a self-contained test case (but are you sure this is not a PostGIS bug?) ... What does SELECT postgis_full_version() show? If you still have the 8.1.3 system then please post the output from both 8.1.3 and 8.1.4. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2629: libpq - Cannot deallocate prepared statement created with PQprepare()
On Fri, Sep 15, 2006 at 07:39:32AM +, Andy McCurdy wrote: result = PQprepare(conn, MyQuery, select * from pg_stat_activity, 0, NULL); /* THE FOLLOW PQEXEC() FAILS. Error message says: ERROR: prepared statement myquery does not exist */ result = PQexec(conn, DEALLOCATE MyQuery); You prepared a mixed-case identifier so you'll need to quote it in SQL statements to preserve its case. Unquoted identifiers are folded to lowercase, as the error message shows. result = PQexec(conn, DEALLOCATE \MyQuery\); See Identifiers and Key Words in the SQL Syntax chapter of the documentation for more information about quoted identifiers. http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2572: ALTER TABLE ADD COLUMN
On Sat, Aug 12, 2006 at 11:53:08AM +, Emil J. wrote: I create some function (PLPGSQL): . BEGIN ALTER TABLE sch.table ADD COLUMN abc INTEGER; RETURN NULL; END; After i called it, nothing happend. No Error, No Exception, No effect - no column added. Works here. Is it possible that the calling transaction rolled back or that it hadn't committed yet and you looked at the table in another transaction? Can you provide a complete test case? Example: test= CREATE TABLE foo (id integer); CREATE TABLE test= CREATE FUNCTION test() RETURNS void AS $$ test$ BEGIN test$ ALTER TABLE foo ADD COLUMN newcol integer; test$ END; test$ $$ LANGUAGE plpgsql; CREATE FUNCTION test= \d foo Table public.foo Column | Type | Modifiers +-+--- id | integer | test= SELECT test(); test -- (1 row) test= \d foo Table public.foo Column | Type | Modifiers +-+--- id | integer | newcol | integer | -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] distance operator dont work
On Sat, Aug 12, 2006 at 08:52:48AM +0200, bernard snyers wrote: I have a table containing polygon and I try a distance operator which fails. (select * from mytable order by point(10,10) - mycol) Postgres responds that the operator - between a point and a polygon is unknown. I look in the pg_operator.h (in src/include/catalog) where I think all builtin operators are defined and found these definitions Are you aware that you can query the system catalogs with SQL instead of digging into header files? See also psql's \do command. http://www.postgresql.org/docs/8.1/interactive/catalogs.html http://www.postgresql.org/docs/8.1/interactive/catalog-pg-operator.html http://www.postgresql.org/docs/8.1/interactive/app-psql.html and there is none between a point and a polygon. (600 604) (furthermore the 628 type is defined in ptypes.h as not implemented. I did the following test, I patch the line DATA(insert OID = 613 ( - PGNSP PGUID b f 600 628 701 0 00 0 0 0 dist_pl - - )); replacing 628 by 604 , compiling and reinstalling everything including the database The dist_pl function expects a line argument; calling it with a different type could cause unexpected results or even a server crash. And are you aware that you can use CREATE OPERATOR instead of hacking the source code and recompiling? http://www.postgresql.org/docs/8.1/interactive/sql-createoperator.html and It works (I didnt verify the result) In what sense does it work if you didn't verify the result? So I am wondering , if I miss something, do I have to convert my point in a degenerated circle or polygon) or do you forget one definition ? I'd guess that certain features remain unimplemented due to lack of interest; maybe one of the developers can comment on the history of support for geometry operations. A nice thing about PostgreSQL is that it's extensible without having to hack the source code: you can write your own functions in PL/pgSQL or C or some other language and create operators that call those functions. Such customizations survive migrations via database dumps so you don't have to remember to patch the code every time you upgrade. If you're working with spatial data then you might want to check out PostGIS: http://www.postgis.org/ -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2553: Outer join bug
[Please copy the mailing list on replies so others can participate in and learn from the discussion.] On Fri, Jul 28, 2006 at 09:54:42AM -0500, Steven Adams wrote: I wanted the row to show whether or not there was a matching row in the other table, but I wanted to return exactly 1 row. As Tom Lane already pointed out, you're probably needing a WHERE clause. Does this do what you want? SELECT ia.name, iac.internal FROM information_assets AS ia LEFT OUTER JOIN information_asset_categories AS iac ON ia.category_id = iac.id WHERE ia.id = 21; -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2553: Outer join bug
On Thu, Jul 27, 2006 at 07:30:01PM +, Steven Adams wrote: select ia.name, iac.internal from information_assets as ia left outer join information_asset_categories as iac on(ia.category_id = iac.id) and ia.id = 21 This causes all rows in information_assets to be returned despite the and clause. Adding join information_assets as ia2 on(ia.id = ia2.id) after the outer join corrects this. http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-JOIN LEFT OUTER JOIN First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table unconditionally has at least one row for each row in T1. The and ia.id = 21 expression is part of the outer join condition that restricts rows from information_asset_categories (T2); it doesn't restrict rows from information_assets (T1). If you don't want all rows from information_assets then why are you using an outer join? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2549: problem with NATURAL JOIN
On Tue, Jul 25, 2006 at 12:58:55AM +, Kayteck wrote: I have two tables joined by foreign key id_przelewu, and for some rows results of queries with NATURAL JOIN and JOIN ... USING (...) differs ! I've readed that NATURAL JOIN is only shorthand for the second method, but this results shows that's not true: [...] select id_przelewu,id_zamowienia from zamowienia natural join przelew where id_klienta=4999; id_przelewu | id_zamowienia -+--- (0 rows) Do zamowienia and przelew have column names in common in addition to id_przelewu? NATURAL JOIN uses all common column names, not just those specified in a foreign key constraint. http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-FROM Finally, NATURAL is a shorthand form of USING: it forms a USING list consisting of exactly those column names that appear in both input tables. http://www.postgresql.org/docs/8.1/interactive/sql-select.html#SQL-FROM NATURAL is shorthand for a USING list that mentions all columns in the two tables that have the same names. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2533: PostGIS- envelope function returns wrong results
On Sun, Jul 16, 2006 at 01:11:53PM +, Vania Bogorny wrote: I would like to report an error with the evelope function in PostGIS (Geos library). PostGIS and GEOS are separate projects from PostgreSQL. Questions and bug reports should go to their respective mailing lists and bug trackers. http://postgis.refractions.net/support/ According to the OGC, two geometries may only overlap if they are both of the same type. I performed a query in PostGIS with 2 objects one polygon and on multi-line. using the overlaps operation with the envelope function this query returned 71 rows. It should return zero, since polygons and lines may cross, an not overlap. Indeed, the crosses operations returns zero rows among this objects using the evelope function. If you report this to the PostGIS project then please provide an example query that demonstrates the behavior you're seeing. The PostGIS folks might also want to know what versions of PostGIS and GEOS you're running. You mention that you're using envelope() but you don't say how -- are you converting the multilinestring to a polygon with envelope() and using that polygon as one of the parameters to overlaps()? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2526: records lost
On Wed, Jul 12, 2006 at 08:58:43PM +, wee goh wrote: i discovered records in my postgresql database missing every 6 - 8 months. i supsect those not updated or amended are the ones that are gone, still wondering the real reason behind ? i doubt is lack of vaccum as my transactions are minnimal, inserting about 200 records each month and updating about 1000 records each month. How much querying are you doing? Are you vacuuming at all? What's the output of the following command? SELECT datname, age(datvacuumxid) AS vage, age(datfrozenxid) AS fage FROM pg_database; Do the server logs contain any unusual messages? Have you had any hardware problems or system crashes? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2521: pg_restore is hanging
On Sun, Jul 09, 2006 at 08:15:27AM +, Simon, Attila wrote: We have dumped a database with pg_dump and tried to restore it. Then the pg_restore program started to hanging, does not give any info even the verbose flag has been turned on. But the dmp file can be executed as an SQL script. Can you give me any info about? What were the exact pg_dump and pg_restore commands you ran? When you say that pg_restore started hanging, do you mean that it displayed some output and then hung, or that it never displayed anything? Is it possible that you ran pg_restore without any arguments and that it's waiting to read from standard input (the terminal window)? If the dump file contains SQL statements then why are you using pg_restore instead of psql? -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2481: select from table's join with geometries doesn't go
On Fri, Jun 16, 2006 at 11:16:50AM +0200, Emilia Venturato wrote: Did the segmentation fault leave a core dump in your $PGDATA directory or somewhere beneath it? If not then you might need to adjust your coredumpsize resource limit. I understand it was psql to crash, not postgresql. Postgres doesn't stop. Maybe this could explain why create table go well and only select doesn't go. Did psql create a core dump? If not then check your coredumpsize resource limit. For example, if you're using bash, then what's the output of ulimit -c? If it's 0 then run ulimit -c unlimited. With a core dump you can use a debugger to get a stack trace that should show where the problem is. Making test I found also that query plan changes if I select geometric field or not. Particulary It seems have problem with merge condition: The query plan shouldn't affect psql's behavior but selecting different columns might. Notice that the estimated column width is much higher when you select the geometry column than when you don't: [with] Merge Join (cost=1184.56..1415.71 rows=9222 width=78224) (actual time=259.035..355.384 rows=18444 loops=1) [without] Hash Join (cost=52.67..483.28 rows=9222 width=113) (actual time=3.113..28.000 rows=18444 loops=1) I prepared a file.zip with problem summary and data. It's 16 Mb. It's downloadable from http://www.faunalia.it/download/bug2481.tar.gz A HEAD request against that file shows it to be 116M (121747346), not 16M, and it appears to be on a slow link (curl estimates over an hour to download). Can you create a smaller test case? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2481: select from table's join with geometries doesn't go
On Fri, Jun 16, 2006 at 06:53:28AM -0600, Michael Fuhr wrote: The query plan shouldn't affect psql's behavior but selecting different columns might. Notice that the estimated column width is much higher when you select the geometry column than when you don't: [with] Merge Join (cost=1184.56..1415.71 rows=9222 width=78224) (actual time=259.035..355.384 rows=18444 loops=1) That's a lot of data -- are you aware that psql (via libpq) fetches the entire result set before displaying it? In most cases 18444 rows wouldn't be a problem, but with rows that wide it becomes a big problem because the client has to store it all in memory. I wonder if that's causing psql to segfault, although I'd expect a graceful error like out of memory for query result unless maybe psql consumes so much memory that the OS has problems. How much memory does the box have and what's your datasize resource limit? Do you get the segfault if you LIMIT the result set to a small number of rows? If you really need all that data then try using a cursor so you can fetch data a few rows at a time instead of all at once. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2481: select from table's join with geometries doesn't go
On Thu, Jun 15, 2006 at 11:48:37PM -0400, Tom Lane wrote: Emilia Venturato [EMAIL PROTECTED] writes: Postgis developper said it could be a postgresql bug. Or it could be a postgis bug. Without a test case we can use to reproduce the problem, it's all speculation. Please send a complete, self-contained test case... This report resembles a message Emilia posted in postgis-users a couple of weeks ago. The only public discussion is a request for the PostGIS version and copy of the data: http://postgis.refractions.net/pipermail/postgis-users/2006-June/012281.html http://postgis.refractions.net/pipermail/postgis-users/2006-June/012282.html Emilia, did you and Sandro (strk) have off-list discussion about this problem? What do version() and postgis_full_version() return? What happens if you select the geometry column without a join, i.e., SELECT the_geom FROM wwf_terr_ecos_multigeom WHERE ...? Do you get the segmentation fault with the original query if you select AsText(the_geom) or AsEWKT(the_geom) instead of just the_geom? Did the segmentation fault leave a core dump in your $PGDATA directory or somewhere beneath it? If not then you might need to adjust your coredumpsize resource limit. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2451: Short column names return no values within function
On Tue, May 23, 2006 at 03:27:01AM +, Alex Weslowski wrote: Below is code for duplicating this error. Fields Peg and Rs03 and Rs12 are absent from returned record (either Record or Cursor) even though the values in the table are not null. The function declares variables with the same names as table columns; that makes queries like SELECT Symbol, RS03, RS12, Peg ... ambiguous because it's not clear whether those names refer to columns or to variables. Problem might be related to type conversion (NULL converts to '' which has no meaning to INT or NUMERIC). Problem is fixed by renaming columns to Peg_Ratio and RS03RS and RS12RS. So, there is something more going on here, related to length of column name. Type conversion and label length aren't relevant -- the problem is due to using the same label to refer to multiple things. Use different names for the variables or qualify the column names in the query (SELECT t.symbol, t.rs03, t.rs12, t.peg FROM testbug AS t ...). -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] constraints tableoid [pgsql8.1]
On Tue, Apr 11, 2006 at 03:11:46PM +0800, ??? ??? wrote: jw=# CREATE TABLE base ( CHECK (tableoid = 'base'::regclass) ); CREATE TABLE jw=# \d base Table public.base Column | Type | Modifiers +--+--- Check constraints: base_tableoid_check CHECK (tableoid = 'base'::regclass::oid) jw=# INSERT INTO base DEFAULT VALUES ; ERROR: new row for relation base violates check constraint base_tableoid_check Check the constraint with a function that logs its arguments and you'll see what's happening: test= CREATE FUNCTION toid_check(oid, oid) RETURNS boolean AS $$ test$ BEGIN test$ RAISE INFO 'toid_check(%, %)', $1, $2; test$ RETURN $1 = $2; test$ END; test$ $$ LANGUAGE plpgsql IMMUTABLE STRICT; CREATE FUNCTION test= CREATE TABLE base (CHECK(toid_check(tableoid, 'base'::regclass))); CREATE TABLE test= INSERT INTO base DEFAULT VALUES; INFO: toid_check(0, 540339) ERROR: new row for relation base violates check constraint base_tableoid_check Apparently a new row's tableoid isn't set until the row is actually inserted. Tableoid would be set in an AFTER trigger, but if the intent is to prevent inheritance then enforcing the constraint with a trigger on the base table wouldn't work because triggers aren't inherited. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2379: Duplicate pkeys in table
On Thu, Apr 06, 2006 at 08:12:31AM -0400, Alvaro Herrera wrote: Please do a SELECT xmin, xmax, cmin, cmax FROM xxx where id = 24613; if you still have that particular manifestation. Also, you'll probably need to set enable_indexscan to off prior to running the above query. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2380: Sequence problem
On Thu, Apr 06, 2006 at 10:04:03AM +, Alex Fomin wrote: While using the following function: --- nextval(sequence_name) returns currval(sequence_name) -1 --- while +1 is expected. It happens only sometimes, no dependency can be found. Could you provide a complete test case? That is, all SQL statements that somebody could execute in an empty database to reproduce the problem. It doesn't have to be 100% reproducible as long as it does exhibit the behavior every once in a while (an indication of how often or under what circumstances, if known, would be helpful). Is it possible that another session is altering the sequence to start with a lower value? Have you perchance set the sequence's CACHE setting to a value other than 1 (one)? What's the output of SELECT * FROM sequence_name? Are you making queries from more than one session? Are you using connection pooling? -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2306: Duplicate primary key
On Tue, Mar 07, 2006 at 04:43:18PM +, Andreas Jung wrote: PostgreSQL version: 7.4.9 7.4.12 is the latest in that branch; it contains several bug fixes since 7.4.9. This gives me two rows with the same id=5077: Toolbox2Test=# select * from hierarchy where id = 5077 order by id; What's the output of the following command? SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id = 5077 ORDER BY id; Search for all rows with id=5077 returns this: Toolbox2Test=# select * from hierarchy where id = 5077; [...] (1 row) Does the same query return different results depending on whether you use an index scan or a sequential scan? What do you get for these queries? SET enable_seqscan TO on; SET enable_indexscan TO off; SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id = 5077; SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id = 5077; SET enable_seqscan TO off; SET enable_indexscan TO on; SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id = 5077; SELECT ctid, xmin, xmax, * FROM hierarchy WHERE id = 5077; -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2289: insert into tables not working
On Tue, Feb 28, 2006 at 09:24:35AM +, Abhilash Krishnan wrote: I am having an application using java, jsp, struts and hibernate with a backend of postgresql. Here, during some operations, although the insert query is generated by hibernate and can be seen in the console, the data is not getting inserted into the database. Any idea why it happens ? Did you commit the transaction that performed the insert? The effects of a transaction's operations aren't visible to other transactions until the transaction commits. Also, if a transaction encounters an error its operations will be rolled back unless you protect them with savepoints. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2289: insert into tables not working
[Please copy the mailing list on replies.] On Wed, Mar 01, 2006 at 08:55:29AM -0800, Abhilash Krishnan wrote: ya the transactions are commited also no error has been shown in any of the operations How are you determining that data isn't getting inserted? If you do a select in the same transaction as the inserts, do you see the data? If you commit the transaction and then start a new transaction, does a select in the new transaction see the data? If concurrent transactions aren't able to see the data even after it's committed, what isolation level are those transactions using? A SERIALIZABLE transaction won't see data committed by other transactions after its snapshot has been taken. If none of this helps then could you post a series of steps that somebody could use to duplicate the problem? -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] Re : BUG #2251: NOT IN clause is not working correctly
On Sun, Feb 26, 2006 at 07:38:32PM +0530, Dhanaraj wrote: I am running a query: select * from A t1 where t1.id not in (select t2.A_id from B t2); It returns 0 rows. Now I run (select t1.id from A t1) except (select t2.A_id from B t2); And now Postgres correctly returns records from A that are not referenced by B. Table B probably has some NULL values for A_id, so the first query's NOT IN expression returns NULL instead of true because it's indeterminate whether t1.id is in the set (NULL means unknown). Here's an example: CREATE TABLE a (id integer PRIMARY KEY); CREATE TABLE b (a_id integer REFERENCES a); INSERT INTO a VALUES (1); INSERT INTO a VALUES (2); INSERT INTO b VALUES (1); INSERT INTO b VALUES (NULL); SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM B); id (0 rows) SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM b WHERE a_id IS NOT NULL); id 2 (1 row) According to past discussion this behavior is per the SQL specification. Search the list archives for more information. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] Re : BUG #2251: NOT IN clause is not working correctly
On Sun, Feb 26, 2006 at 07:38:32PM +0530, Dhanaraj wrote: I tested this exampe. It works fine in Solaris platform (postgres 8.1.2 released recently by sun) Try the same example in some other version. If this is true, the changes need to be done for a particular version of postgres.. Sorry, I overlooked that this message was a reply because the original wasn't quoted. The behavior is data-dependent -- if you add some NULLs as in the example I just sent then you should be able to reproduce it on any platform in any version of PostgreSQL (at least any modern version). According to past discussion it's per the spec. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2265: CREATE TABLE when AUTOCOMMIT is OFF
Please copy the pgsql-bugs mailing list on replies. Also, if you've submitted multiple bug reports then please keep the discussion of each bug in their respective threads. I'm replying only to 2265 because I'm not as familiar with the other problems. On Fri, Feb 17, 2006 at 03:09:48PM +0100, [EMAIL PROTECTED] wrote: Now I configured query logging and I saw what was wrong. First I executed SELECT rel FROM versions but the 'versions' table didn't exist. So I thought that I can create this 'versions' table in the same transaction. In PostgreSQL 8.0.4 this worked, but in 8.1.3 I got the error message. When you say that this worked in 8.0.4 but not in 8.1.3, what exactly do you mean by this? If the SELECT failed then any subsequent command in the same transaction should also have failed; 8.0 and 8.1 shouldn't differ in that respect, at least not in the backend. If you can demonstrate otherwise then please post a repeatable test case (i.e., a set of SQL statements that can be run against an empty database in a new session or a new transaction). Try running tests with psql as well as with ODBC to see if they behave differently. When executing COMMIT after the SELECT statement, I was able to do the CREATE TABLE. I think that this behaviour is desired in 8.1.3, am I right? It surely has to do with the two-phase commit. Are you using two-phase commit? Why do you think it's surely involved? Let's see a complete test case before jumping to conclusions. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2265: CREATE TABLE when AUTOCOMMIT is OFF
On Thu, Feb 16, 2006 at 01:21:18PM +, Gerhard Lutz wrote: Error while executing the query; ERROR: current transaction is aborted, commands ignored until end of transaction block (7) Some previous command failed so no more commands in this transaction will be allowed. If you don't know what command failed then you could configure query logging. In PostgreSQL 8.0.4 I was able to create a table in AUTOCOMMIT mode OFF without any error. Is this a bug in 8.1.3? The error is apparently happening before the CREATE TABLE command. Let's see what earlier command failed and why. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2260: PGCrypto Memory Problem
On Tue, Feb 14, 2006 at 05:28:25PM +, Daniel Blaisdell wrote:
Problem Query:
select * from table where md5password = crypt('password',salt)
The first time this query is run, I see the postgres process bump up to 8MB
of ram from where it initializes.
On subsequent issues of the same query the postgres's process memory
footprint grows each time.
I can reproduce this in 8.1.3 on FreeBSD 6.0 and Solaris 9. Here's
a standalone test case:
SELECT crypt(x::text, '$1$salt') FROM generate_series(1, 500) AS g(x);
Running the query with 'salt' instead of '$1$salt' doesn't exhibit
a memory leak, not even with more iterations from generate_series.
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2260: PGCrypto Memory Problem
On Wed, Feb 15, 2006 at 01:43:18PM -0500, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: I can reproduce this in 8.1.3 on FreeBSD 6.0 and Solaris 9. Here's a standalone test case: SELECT crypt(x::text, '$1$salt') FROM generate_series(1, 500) AS g(x); Interesting, because I see no leak with this example on Fedora 4 or HPUX. Platform dependency is sounding more and more likely. Did you test OpenSSL builds? Both of my systems are built with OpenSSL and that causes pgcrypto to use different code in some places (e.g., px_find_digest() in internal.c and openssl.c). I'll build and test a non-OpenSSL version when I get a chance. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2260: PGCrypto Memory Problem
On Wed, Feb 15, 2006 at 02:28:33PM -0500, Tom Lane wrote: Michael Fuhr [EMAIL PROTECTED] writes: Did you test OpenSSL builds? Nope, I did not, and that's a good point. Will try again with openssl. My non-OpenSSL build shows no memory leak, so the leak and OpenSSL seem to be correlated. I'd be more inclined to suspect a bug in pgcrypto's OpenSSL-specific code than in OpenSSL itself. Will keep digging. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] Please get me out of this ASAP
On Thu, Feb 09, 2006 at 04:22:26AM +, shyam nair wrote:
Now I have a situation like the sequence id is repeating. This
applicaton is running since from 1999, this is the first time we
get such a result.
What version of PostgreSQL are you running? Hopefully you've
upgraded since 1999.
1. This is the code we used to create sequence seq_type_code
CREATE SEQUENCE public.seq_type_code
INCREMENT 1 MINVALUE 10
MAXVALUE 9223372036854775807 START 10
CACHE 1;
When do you create this sequence? Is it possible that it got dropped
and recreated, causing it to start at the beginning again? Or that
somebody used ALTER SEQUENCE or setval() to reset the start value?
2 This is query we used to insert value, here we were using the sequence.
insert into
tour_type(type_code,type_name,from_tour_num,end_tour_num)
values(nextval('seq_type_code'),'type_name',tourRangeFrom,tourRangeTo);
3. This is the result we getting, see type code is repeating here
type_code | type_name | from_tour_num | end_tour_num
---+---+---+--
10| TEST TOUR | 1|
99
10| FRANCE TEST TOUR | 100 | 199
11| GERMENY TEST TOUR | 200 | 299
12| HOLLAND TEST TOUR | 300 | 399
If this is a problem then why don't you have a primary key or unique
constraint on type_code? That doesn't explain why you're getting
duplicates, but at least you'd get an error when it happens. Or
do you have such a constraint and it isn't working?
Is it possible that somebody inserted a record with an explicit
type_code? That is, by specifying 10 instead of nextval('seq_type_code')?
Or that somebody updated an existing record? How many times has
the problem happened? If more than once, how often? Can you think
of anything that happened with the database around the time the
problem started? Have you enabled statement logging to see what
statements are actually being executed?
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2236: extremely slow to get unescaped bytea data
On Thu, Feb 09, 2006 at 12:46:46PM -0300, Alvaro Herrera wrote: I note in the PHP 4 sources that the PQunescapeBytea function seems to have been copied there, for the benefit of PostgreSQL 7.2 users. It says that it comes from 7.3 but I don't see any sscanf call. There is no PQunescapeBytea call in the whole source that I can see, so my guess is that the libpq function is not called at all. So this may be a PHP bug rather than a Postgres bug. The OP claimed to be using PHP 5.1.2, which does have a call to PQunescapeBytea(), although it also has the old code you're seeing and a HAVE_PQUNESCAPEBYTEA macro that determines which to use. Interesting that the command line php and the Apache module behave differently. I wonder if ldd would show the php executable and libphp5.so linked against different versions of libpq; that would add weight to Tom's suggestion that an old libpq might be responsible. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2236: extremely slow to get unescaped bytea data
On Sat, Feb 04, 2006 at 04:06:11PM -0800, Kalador Tech Support wrote: I've since isolated the problem to the unescape_bytea function not the SELECT. I inserted the same image to a bytea column using base64 encoding, and extracted it from the table (using base64 decoding) and this worked very fast (1 second). So, it is the unescape_bytea function that is to blame. pg_unescape_bytea is fast here; I just unescaped an 850K jpeg image in about 0.18 seconds on a slow (500MHz) machine. How did you determine that pg_unescape_bytea was the problem? What does something like the following show? $tstart = microtime(true); $data = pg_unescape_bytea(pg_fetch_result($res, 'data')); $dt = microtime(true) - $tstart; header(Content-Type: text/plain); printf(unescape time = %.3fms, %d bytes\n, $dt * 1000.0, strlen($data)); -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2224: unlogical syntax error
[Please copy the mailing list on replies.] On Tue, Jan 31, 2006 at 08:53:44AM +0100, Hnoch Herv wrote: Thank for you response I have never written this query : I think it is JDBC that have generated it ... My query (written by me) is : select id_caisse as caisse from FROM adm_pat WHERE nip = '20020523' I know I have to use as statement ... I'd never disturb you for a simple syntax error ... The above query does have a syntax error (from FROM), although the query in your original report looked correct. Jdbc version : jdbc3 8.1.404 I downloaded postgresql-8.1-404.jdbc3.jar onto a Solaris 9 box running PostgreSQL 8.1.2 and wrote a test program to run your original query: select id_caisse as caisse from adm_pat where nip = '20020523' The query ran successfully and logged the following: LOG: statement: PREPARE unnamed AS select id_caisse as caisse from adm_pat where nip = '20020523' LOG: statement: BIND LOG: statement: EXECUTE unnamed [PREPARE: select id_caisse as caisse from adm_pat where nip = '20020523'] The very stange think is, if I write : select nip, id_caisse as caisse from FROM adm_pat WHERE nip = '20020523' There is no syntax error ! There should be a syntax error due to from FROM; please post the actual queries you're running. It might also be helpful if you could post a simple but complete program so we can see everything you're doing. You might also try asking in the pgsql-jdbc list to see if anybody there has heard of this problem. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2224: unlogical syntax error
On Mon, Jan 30, 2006 at 10:47:10AM +, Herv Hnoch wrote: CETLOG: statement: PREPARE unnamed AS SELECT adm_pat.nip, id_caisse caisse FROM adm_pat WHERE nip = '20020523' CETERROR: syntax error at or near caisse at character 33 Are you writing this query or is JDBC generating it? The AS keyword is required for column aliases; its absence is causing the syntax error. test= CREATE TABLE foo (abc text); test= SELECT abc AS xyz FROM foo; xyz - (0 rows) test= SELECT abc xyz FROM foo; ERROR: syntax error at or near xyz at character 12 LINE 1: SELECT abc xyz FROM foo; ^ -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2217: serial in rule and trigger
On Thu, Jan 26, 2006 at 07:17:57PM +, ATTILA GATI wrote:
create table test(id serial, txt varchar);
create table mon(n int);
create rule monitor as on insert to test do
insert into mon values (NEW.id);
insert into test (txt) values ('xxx');
What I expect is to get the latest id written in
table mon whenever I insert a data into table test.
However test.id will be incremented by 2!
This isn't a bug, it's a misunderstanding of how rewrite rules work.
NEW.id in the rule is rewritten as whatever expression that column
had in the original query, so if id in the original query is evaluated
as nextval('test_id_seq') then it will be the same in the rule;
hence, nextval() gets called twice. See the archives for numerous
past discussion. Try using a trigger instead of a rule.
However - although the relevant part of the documentation is identical for
both versions - in case
of version 8.1 I found now holes when the transaction was aborted for some
reason (not in the above example,
just without a trigger or rule).
So there must be a difference between the 2 versions, but the documentation
hasn't been modified.
Sequences don't roll back so they can have holes; that's long-standing
behavior that hasn't changed. Can you provide a test case that
behaves differently in different versions of PostgreSQL?
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2208: Low performance on select
On Wed, Jan 25, 2006 at 12:32:31PM +, Fahri CAKIROGLU wrote: Consecutive selects from three different tables causes low performance(about 1500 ms). Same select count from two tables gives good performance(about 30 ms). This might be due to caching. When you query two tables all of the pages you need might be cached, but when you query a third table some of the pages from the other tables might be evicted from the cache. When you query one of those tables again the pages have to be fetched from disk. How big are the tables in question? All searched keys are indexed and individual execution of each query is very fast. Could you post the EXPLAIN ANALYZE output of each query? Have you tuned any of your postgresql.conf settings, in particular shared_buffers? How much memory do you have? -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2198: Now returns always same date and time during a session
On Mon, Jan 23, 2006 at 11:40:43AM +, Jacques Gollion wrote: The following functions returns the first time the right date and time but when called at several date and time returns the date that was returned at the first call. See Current Date/Time in the documentation: http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT It is important to know that CURRENT_TIMESTAMP and related functions return the start time of the current transaction; their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the 'current' time, so that multiple modifications within the same transaction bear the same time stamp. There is also the function timeofday() which returns the wall-clock time and advances during transactions. To get again the right date, it is necessary do disconnect and reconnect. Do you have autocommit disabled? I'd guess all of your function calls are happening in the same transaction. You shouldn't have to reconnect; starting a new transaction should work. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2180: log_statement=mod does not work
On Wed, Jan 18, 2006 at 03:38:17PM +, Gilles wrote: When I configure in the file postgresql.conf: log_statement=mod I don't have the update, delete, insert queries logged like it says in the documentation : http://www.postgresql.org/docs/8.1/interactive/runtime-config-logging.html Could you tell me, what I need to do to get these queries logged? Works here. Did you restart or reload the server after making the change? Are you sure you changed the right postgresql.conf (this can be a problem if you have multiple versions of PostgreSQL installed)? What does SHOW log_statement show? Are you sure you're looking in the right log file? Do you see other log entries? -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2178: NOT IN command don't work
On Tue, Jan 17, 2006 at 10:00:28PM +, Daniel Afonso Heisler wrote: When i run the following query, postgreSQL return TRUE. # SELECT true WHERE 1 NOT IN (2,3); But, when i run the next query, it don't return TRUE # SELECT true WHERE 1 NOT IN (2,NULL,3); The expression 1 NOT IN (2,NULL,3) evaluates to NULL because NULL means unknown. This comes up occasionally; see the archives for past discussion. http://archives.postgresql.org/pgsql-sql/2005-12/msg00219.php http://archives.postgresql.org/pgsql-sql/2005-10/msg00227.php -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] createlang plpgsql failed
On Tue, Jan 10, 2006 at 10:14:42AM +0530, Jeevanandam, Kathirvel (IE10) wrote: We are getting error as bus error while running the createlang command createlang -d dbname plpgsql Is the createlang program getting the bus error or is it the backend? Does anything show up in the server logs? What operating system and version? What version of PostgreSQL and where did you get it (installed a package, built it yourself from source, etc.)? If you got a core dump, can you get a stack trace from it? -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2157: Disabling trigger
On Mon, Jan 09, 2006 at 03:35:47PM +, Jiri Klepac wrote: I was tryiing to disable trigger for specific table. ALTER TABLE tbl DISABLE TRIGGER trg_name; This is well documented in manual for ALTER TABLE command. Are you sure you're looking at the documentation for the version you're running? DISABLE TRIGGER is in the documentation for 8.1 but not for 8.0. http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2148: Crashes on simple statement
On Fri, Jan 06, 2006 at 10:30:28AM -0600, Jim C. Nasby wrote: OS X, Version 8.1.1: decibel=# select ms from (select 1) ms; server closed the connection unexpectedly This query doesn't crash for me with the latest CVS. It might be the same problem Tom fixed a few weeks ago: http://archives.postgresql.org/pgsql-bugs/2005-12/msg00128.php http://archives.postgresql.org/pgsql-committers/2005-12/msg00296.php http://archives.postgresql.org/pgsql-committers/2005-12/msg00297.php -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] autovacuum process (PID ...) was terminated by signal 11
On Wed, Jan 04, 2006 at 12:20:28PM -0500, Jaime Casanova wrote: On 1/4/06, Brian Hirt [EMAIL PROTECTED] wrote: that's strange, because I'm running 8.1.1. what Tom is saying is that a patch was applied after 8.1.1 was launched... Is that what Tom is saying? The commit message he posted had a date of 2005-11-28; 8.1.1 wasn't tagged until 2005-12-08. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2136: plperl doesn't work, plperlu - yes
On Fri, Dec 30, 2005 at 09:43:52AM +, Robert Osowiecki wrote:
Creation of any plperl function gives error message:
ERROR: creation of Perl function failed: Can't locate object method new
via package Safe at line 1.
(in cleanup) Can't call method reval on an undefined value at line
1.
Can you use the Safe module in standalone Perl scripts? What happens
if you run the following?
#!/usr/bin/perl
use strict;
require Safe;
print Safe::VERSION = $Safe::VERSION\n;
my $foo = new Safe('foo');
print test = , $foo-reval(test), \n;
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2131: SQL Query Bug ?
On Mon, Dec 26, 2005 at 03:47:36PM +, kenichi nakanishi wrote:
I found something strange result when using a following sql sentence,
select xxx || ' / ' || || ' / ' || as aaa from TABLE,
sometime I could get empty results.
When using same scentence on linux platform, I could get correct results.
So I think it's a bug on windows version.
Do the Linux and Windows platforms have the same data? Might any
of the columns on the Windows system be NULL? Concatenating anything
with NULL results in NULL, so that could be the problem.
test= CREATE TABLE foo (col1 text, col2 text);
CREATE TABLE
test= INSERT INTO foo (col1, col2) VALUES ('aaa', 'bbb');
INSERT 0 1
test= INSERT INTO foo (col1, col2) VALUES ('ccc', NULL);
INSERT 0 1
test= INSERT INTO foo (col1, col2) VALUES (NULL, 'ddd');
INSERT 0 1
test= SELECT col1, col2, col1 || col2 FROM foo;
col1 | col2 | ?column?
--+--+--
aaa | bbb | aaabbb
ccc | |
| ddd |
(3 rows)
If you want to treat NULL as an empty string then use COALESCE:
test= SELECT col1, col2, COALESCE(col1, '') || COALESCE(col2, '') FROM foo;
col1 | col2 | ?column?
--+--+--
aaa | bbb | aaabbb
ccc | | ccc
| ddd | ddd
(3 rows)
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2117: inconsistency in sum
On Thu, Dec 15, 2005 at 11:03:55AM +, Adam Kolany wrote: can you explain this? == stoff= select sum(wplyw),sum(wydatek), sum(wplyw)-sum(wydatek) from rachunki where okres6; sum | sum | ?column? ---+-+-- 73745 | 6712.55 | 67032.5 (1 row) What data types are wplyw and wydatek? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2117: inconsistency in sum
[Please copy the mailing list on replies.] On Thu, Dec 15, 2005 at 07:32:54PM +0100, Adam Kolany wrote: Michael Fuhr napisa?(a): On Thu, Dec 15, 2005 at 11:03:55AM +, Adam Kolany wrote: stoff= select sum(wplyw),sum(wydatek), sum(wplyw)-sum(wydatek) from rachunki where okres6; sum | sum | ?column? ---+-+-- 73745 | 6712.55 | 67032.5 (1 row) What data types are wplyw and wydatek? they were float(2) casting them into numeric helped, so I have changed the types of the to numeric, instead of float(2) this is however a bug, I think. float(2) gives you a real, aka float4 (32-bit floating point). As the documentation points out, that type has a precision of 6 decimal digits; you seem to object to the rounding: test= SELECT 73745::float8 - 6712.55::float8; ?column? -- 67032.45 (1 row) test= SELECT 67032.45::float4; float4 - 67032.5 (1 row) test= SELECT 73745::float4 - 6712.55::float4; ?column? -- 67032.5 (1 row) That's arguably not a bug: you've requested a low-precision data type so you have to expect discrepancies around that 6th digit of precision. If you need greater precision then use a double precision (float8) type, or if you need exact precision (e.g., for handling money) then use numeric. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2115: If we CLOSE a Closed Cursor subsequent OPENs fail.
On Wed, Dec 14, 2005 at 06:16:19PM +, David S. Edwards wrote: We are evaluating Postgres for a very large customer who has a lot of legacy software that they have executed with several proprietary RDBMS. We have run into a situation that is common in their batch code. They OPEN a cursor (without WITH HOLD), FETCH rows, a COMMIT occurs, then sometime later they CLOSE then reOPEN the cursor. With Postgres, they are unable to reOPEN the cursor whereas with the other RDBMS they can. The code you posted prints an error that tells what's wrong: do OPEN open sqlcode = -400 ERROR MESSAGE : 'current transaction is aborted, commands ignored until end of transa The server log should show the complete error message: ERROR: current transaction is aborted, commands ignored until end of transaction block After the COMMIT ECPG starts a new transaction, so when the subsequent CLOSE fails the transaction is aborted and no further commands will be allowed. That's standard all-or-nothing transaction behavior: everything succeeds or the transaction must be abandoned. Apparently the other database works differently than PostgreSQL and allows the transaction to continue after certain types of error. In PostgreSQL 8.0 and later you can defend transactions against errors by using savepoints. Declare a savepoint before code that might fail, and if it does fail then rollback to the savepoint. In any case release the savepoint after the section of code that it protects. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2106: EXPLAIN ANALYZE with SELECT query causes a single backend server process to segfault
On Sat, Dec 10, 2005 at 12:57:13PM -0500, Tom Lane wrote: [EMAIL PROTECTED] writes: Description:EXPLAIN ANALYZE with SELECT query causes a single backend server process to segfault Could you provide a self-contained test case, please? Here's a test case: CREATE TABLE foo (x integer); EXPLAIN SELECT CASE x = 1 WHEN true THEN 1 ELSE 0 END AS y FROM foo ORDER BY y; I get a segfault in 8.1.1 with EXPLAIN but not for the query alone. Here's the stack trace: #0 0x081bfdcf in get_rule_expr (node=0x83ac460, context=0xbfbfd4b0, showimplicit=80 'P') at pg_list.h:82 #1 0x081c0e73 in deparse_expression_pretty (expr=0x83ac460, dpcontext=0x83ac7b0, forceprefix=0 '\0', showimplicit=1 '\001', prettyFlags=32, startIndent=32) at ruleutils.c:1388 #2 0x080ef4a5 in explain_outNode (str=0x83ac648, plan=0x83ac388, planstate=0x83ad1f0, outer_plan=0x0, indent=0, es=0x83ac638) at explain.c:1160 #3 0x080ef90d in ExplainOnePlan (queryDesc=0x83ac5f0, stmt=0x835b708, tstate=0x8305b00) at explain.c:282 #4 0x080efca5 in ExplainOneQuery (query=0x83c7580, stmt=0x835b708, tstate=0x8305b00) at explain.c:214 #5 0x080efdcd in ExplainQuery (stmt=0x835b708, dest=0x83c7530) at explain.c:121 #6 0x0818cb2f in PortalRunUtility (portal=0x83ca018, query=0x835b228, dest=0x83c7530, completionTag=0x0) at pquery.c:987 #7 0x0818ce16 in PortalRun (portal=0x83ca018, count=2147483647, dest=0x835b748, altdest=0x835b748, completionTag=0xbfbfd7b0 ) at pg_list.h:81 #8 0x08189127 in exec_simple_query (query_string=0x835b018 EXPLAIN\nSELECT CASE x = 1 WHEN true THEN 1 ELSE 0 END AS y\nFROM foo\nORDER BY y;) at postgres.c:1002 #9 0x0818b693 in PostgresMain (argc=4, argv=0x82e4d98, username=0x82e4d78 mfuhr) at postgres.c:3168 #10 0x081684e6 in ServerLoop () at postmaster.c:2853 #11 0x08169a01 in PostmasterMain (argc=3, argv=0xbfbfecb8) at postmaster.c:943 #12 0x08133612 in main (argc=3, argv=0xbfbfecb8) at main.c:256 -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] date overflows
On Sat, Dec 03, 2005 at 07:53:23PM -0500, Kris Jurka wrote: I'm seeing some date input overflows here. Yep, I noticed this a few days ago while looking at another problem. I probably should have started a new thread. http://archives.postgresql.org/pgsql-hackers/2005-11/msg01563.php -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUGs for function returning a SETOF values on linux postgres 8.0.4, the same function runs correctly on win postgres 8.1
On Wed, Nov 30, 2005 at 02:53:44PM -0700, Susan Fu wrote: I am trying to run a very simple test on the above version. This test works fine on my window 8.1 version. If I am taking out the function on the 8.0.4 version on linux as just the select (SELECT DISTINCT value FROM colors) I can see the results. If run via a function call I got the following error: Failed to execute SQL : SQL select * from tt(); failed : ERROR: control reached end of function without RETURN CONTEXT: PL/pgSQL function tt The PL/pgSQL documentation for 8.0 and earlier says this: The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting a RETURN statement, a run-time error will occur. The 8.1 Release Notes show that this requirement has been removed: * No longer require functions to issue a RETURN statement (Tom) This is a byproduct of the newly added OUT and INOUT functionality. RETURN can be omitted when it is not needed to provide the function's return value. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #2056: to_char no long takes time as input?
On Sun, Nov 20, 2005 at 07:53:50AM +, Nick Addington wrote:
The following code works in 8.0.4 but fails in 8.1.0:
select to_char('1:00 pm'::time,'HH:MM AM');
8.1.0 gives this is the error message:
ERROR: invalid format specification for an interval value
HINT: Intervals are not tied to specific calendar dates.
I saw some discussion on the -hackers list about deprecating
to_char(interval, text), but do you really want to chuck to_char(time,
text)? That's a useful function. Or at least, I was using it...
to_char(time,text) doesn't exist, at least not in 7.3 and later --
you can see that with \df to_char in psql. If you set debug_print_parse
to on and set client_min_messages to debug1, you'll see that the
function being called is funcid 1768, which is
test= select 1768::regprocedure;
regprocedure
to_char(interval,text)
(1 row)
You'll also see that this function's first argument is a function
expression with funcid 1370, which is
test= select 1370::regprocedure;
regprocedure
interval(time without time zone)
(1 row)
So the time value is first converted to an interval and then passed
to to_char(interval,text).
test= select interval('1:00 pm'::time);
interval
--
13:00:00
(1 row)
test= select to_char('13:00:00'::interval,'HH:MM AM');
ERROR: invalid format specification for an interval value
HINT: Intervals are not tied to specific calendar dates.
This looks like the commit that changed the behavior in 8.1 (the
hint was added later):
http://archives.postgresql.org/pgsql-committers/2005-08/msg00200.php
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] Double sequence increase on single insert with RULE on targeted table
On Wed, Nov 16, 2005 at 10:31:10AM +0200, Sarunas Krisciukaitis wrote:
I understand that RULES are like macros.
Strangest thing here is that INSERT to test1 will touch only one
sequence: test1_id_seq.
And it increments test1_id_seq twice during insert with RULE.
Yes, that's a well-known effect of rewriting a query that includes
a call to nextval(). NEW.id in the rule doesn't refer to the value
that's inserted, but rather to the expression that's evaluated to
get that value. Since you didn't provide a value for id it gets
the default: nextval('test1_id_seq'). That expression is used in
both inserts, so the sequence gets incremented twice. See the
archives for numerous past discussions of this behavior.
--
Michael Fuhr
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] Double sequence increase on single insert with RULE on targeted table
On Wed, Nov 16, 2005 at 06:29:40AM +0100, Tomas Zerolo wrote: AFAIK, serials are not guaranteed to produce sequential values; tehy will produce unique values. That means that they can (and sometimes will) jump. In this particular case, however, the behavior is due to the rule on test1: CREATE RULE test1_on_insert AS ON INSERT TO test1 DO INSERT INTO test_log1 (qid) VALUES (new.id); This gotcha comes up occasionally; it's due to the fact that rules are macros. Search the archives for past discussion. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2037: user function call unexpected input out of range
On Fri, Nov 11, 2005 at 09:26:47PM +, Tom wrote: This surfaced when calculating the distances between zip codes using latidtudes and longitudes. When the latitudes and longitudes are the same the distance should be zero. Number 1 gives the error. Number 2 differs by a digit in the last decimal place and works. Number 3 is another test with identical latitudes and longitudes and does calculate zero. If you use psql and increase VERBOSITY you can see where the error is happening: test= \set VERBOSITY verbose test= select find(42.3202,-83.2687,42.3202,-83.2687); ERROR: 22003: input is out of range CONTEXT: SQL function find statement 1 LOCATION: dacos, float.c:1602 dacos is the internal name of PostgreSQL's acos() function. I'd guess the value that acos() receives is a wee bit bigger than 1.0 due to rounding and the inaccuracy of representing floating point numbers in binary; you don't see the problem with another pair of identical locations because the value calculated to the 16th or so digit is slightly different. Your function uses the law of cosines; consider using haversine instead. http://www.movable-type.co.uk/scripts/GIS-FAQ-5.1.html If you're working with geospatial data then you might want to look at PostGIS. http://postgis.refractions.net/ See also PostgreSQL's contrib/earthdistance module. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2034: Wrong time zone IST
On Thu, Nov 10, 2005 at 12:10:27PM +, Neeraj Malhotra wrote: In postgreSQL IST timezone is being used for Israel Standard Time(+2:00) which is incorrect. IST stands for Indian Standard Time(+5:30). Please correct it because it is causing problem in our applications. IST means something different depending on whether you're in India, Israel, or Ireland. This has come up before; allowing users to customize it is on the developers' TODO list but nobody's gotten around to it. http://archives.postgresql.org/pgsql-bugs/2004-01/msg00202.php http://archives.postgresql.org/pgsql-hackers/2004-10/msg00766.php http://www.postgresql.org/docs/faqs.TODO.html In the first message above, Tom Lane suggests hacking src/backend/utils/adt/datetime.c if you want to fix your own system. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2001: Signal 11 after concurrent inserts + updates
[Please copy the mailing list on replies.] On Thu, Oct 27, 2005 at 10:54:51AM -0400, Telin Lin wrote: I have just got a 17M coredump. Do you have a preferred way for me to sent that to you? I can always place it on a ftp site for you download. Thank you. Please post the stack trace from the core dump. Something like this should work if you have gdb installed: gdb /path/to/postgres /path/to/coredump /path/to/postgres means the path to the executable file named postgres. If you're not sure where that is then look in your PostgreSQL startup script or use locate, find, etc. gdb will print some startup info, then it'll present a (gdb) prompt. Type bt and hit Enter/Return (bt means backtrace) (hopefully postgres was compiled with debugging symbols). Copy the output into an email message and post it here. Use q to exit gdb. If Tom Lane gives differing or additional instructions then listen to him, because he's one of the core developers. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2001: Signal 11 after concurrent inserts + updates
On Wed, Oct 26, 2005 at 09:27:52AM +0100, Telin Lin wrote: My postgresql keeps crashing by signal 11 after a number of concurrent inserts + updates. Did you get a core dump? If so, can you get a stack trace from it? In released versions of PostgreSQL core dumps are usually found under the $PGDATA/base/database oid directory unless your system is configured to put them elsewhere. If you got no core dump then you might need to adjust the coredumpsize resource limit that the postmaster runs with. You mentioned that you were using Slony. Do you have any other add-ons? Custom data types, third-party modules, etc.? -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
On Tue, Oct 25, 2005 at 12:48:10PM +1000, Klint Gore wrote: On Tue, 25 Oct 2005 08:51:59 +1000, Russell Smith [EMAIL PROTECTED] wrote: Looks to mee like Daylight Savings has conveniently started. But the elapsed time for those results is only 6 days, 23 hours. That's changed since v7.4.7 I think this item in the 8.1 Release Notes might be relevant: * Add an internal day field to INTERVAL so a one day interval can be distinguished from a 24 hour interval (Michael Glaesemann) Days that contain a daylight savings time adjustment are not 24 hours, but typically 23 or 25 hours. This change allows days (not fixed 24-hour periods) to be added to dates who's result includes a daylight savings time adjustment period. Therefore, while in previous releases 1 day and 24 hours were interchangeable interval values, in this release they are treated differently, e.g. '2005-05-03 00:00:00 EST' + '1 day' = '2005-05-04 00:00:00-04' '2005-05-03 00:00:00 EST' + '24 hours' = '2005-05-04 01:00:00-04' Here's an example and the results from 7.4.9, 8.0.4, and 8.1beta4: \x SET TimeZone TO 'Australia/NSW'; SELECT version(), now(), now() + interval'1 week', now() + interval'168 hours'; -[ RECORD 1 ]--- version | PostgreSQL 7.4.9 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2 now | 2005-10-25 13:35:43.663169+10 ?column? | 2005-11-01 14:35:43.663169+11 ?column? | 2005-11-01 14:35:43.663169+11 -[ RECORD 1 ]--- version | PostgreSQL 8.0.4 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2 now | 2005-10-25 13:35:45.459081+10 ?column? | 2005-11-01 14:35:45.459081+11 ?column? | 2005-11-01 14:35:45.459081+11 -[ RECORD 1 ]-- version | PostgreSQL 8.1beta4 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2 now | 2005-10-25 13:35:47.104595+10 ?column? | 2005-11-01 13:35:47.104595+11 ?column? | 2005-11-01 14:35:47.104595+11 -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
On Mon, Oct 24, 2005 at 11:21:52PM -0400, Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: That's changed since v7.4.7 Yup. '1 week' = '7 days' which is no longer the same as 7*24 hours. In particular, as of 8.1 local noon plus one day is still local noon, even if there was a DST change in between. Adding 24 hours, on the other hand, might give 11am or 1pm. Should 24 hours be the same as 1 * 24 hours? The latter appears to be equal to 1 day, not 24 hours: test= SELECT '2005-10-29 12:00:00-06'::timestamptz + '24 hours'::interval; ?column? 2005-10-30 11:00:00-07 (1 row) test= SELECT '2005-10-29 12:00:00-06'::timestamptz + 1 * '24 hours'::interval; ?column? 2005-10-30 12:00:00-07 (1 row) test= SELECT '2005-10-29 12:00:00-06'::timestamptz + '1 day'::interval; ?column? 2005-10-30 12:00:00-07 (1 row) -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1989: Curious blob problem
On Sun, Oct 23, 2005 at 11:33:35AM +0100, Petr wrote: I'm trying to use blob fields (bytea) and have a curious problem with it (in Delphi). When i'm store the blob stream into DB, the length is 810 bytes. When i'm trying to get data from DB, gives me only 807 bytes. I was try to store entire streams into files and locate differences. Three bytes aren't be in exported file in the middle of the file :-O. Have you done any tests that don't involve Delphi? With psql, for example? This might not be a PostgreSQL problem. Can you look at this problem (i can send you these files). A test case would be useful: all SQL statements, data, and other steps that somebody could use to duplicate what you're doing. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1971: Start Transaction is giving Errors !!!
On Mon, Oct 17, 2005 at 12:50:29PM +0100, Yogaraj. C wrote: When I was executing the procedure (with Transaction), postgres will producing the following error. ERROR: SPI_prepare() failed on START TRANSACTION CONTEXT: PL/pgSQL function sp_example1 line 4 at SQL statement http://www.postgresql.org/docs/7.4/interactive/plpgsql-structure.html Functions and trigger procedures are always executed within a transaction established by an outer query --- they cannot start or commit transactions If anyone know the solution for this, pleas help me. What problem are you trying to solve? PostgreSQL 8.0 introduced savepoints, which you can use in PL/pgSQL via an EXCEPTION clause: http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING Another possibility might be to use dblink to make another connection to the database from within the function and execute transactions over that connection. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1956: Plpgsql top-level DECLARE does not share
On Thu, Oct 13, 2005 at 03:51:15PM +, Karl O. Pinc wrote: I definately do not recall catching any additional errors at compile time as part of the switch to 8. 8.0's syntax checking is minimal; 8.1's will be better. Also, you might not even have plpgsql's lanvalidator function if you restored from an earlier version. What's the result of the following query? SELECT * FROM pg_language WHERE lanname = 'plpgsql'; If lanvalidator is 0 then you won't get even the minimal syntax checks. 8.1 will avoid this problem by creating languages based on entries in a template table (pg_pltemplate). -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #1962: ECPG and VARCHAR
On Thu, Oct 13, 2005 at 02:24:27PM +0100, Charles Wegrzyn wrote: I have code that under 8.0.3 works: VARCHAR t[MAX_TENANT_SIZE+1]; VARCHAR o[MAX_OID_SIZE+1]; In 8.0.4 I found this throws an error during the ecpg step: tenant.ec:375: ERROR: pointer to varchar are not implemented tenant.ec:376: ERROR: pointer to varchar are not implemented ecpg in 8.0.4 seems not to like the macros. I get the same error, but not if I do this: VARCHAR t[256]; VARCHAR o[256]; ecpg in 8.1beta3 works either way. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1962: ECPG and VARCHAR
On Thu, Oct 13, 2005 at 09:49:20AM -0600, Michael Fuhr wrote: ecpg in 8.0.4 seems not to like the macros. I get the same error, but not if I do this: VARCHAR t[256]; VARCHAR o[256]; ecpg in 8.1beta3 works either way. This appears to be the guilty commit, which was made to 7.4, 8.0, and HEAD (8.1): http://archives.postgresql.org/pgsql-committers/2005-08/msg00266.php It was recently fixed in HEAD only: http://archives.postgresql.org/pgsql-committers/2005-10/msg00043.php -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] Problem with COPY in 8.0.3
On Wed, Oct 12, 2005 at 09:09:05AM +0100, Oliver Elphick wrote:
Pg 8.0.3 (Debian package) on AMD64, linux 2.6.12
I am importing a table using COPY. The data is tab-delimited. COPY
seems to be putting the data for one field into the preceding field,
which should contain the empty string.
I couldn't duplicate this in 8.0.4 on either FreeBSD or Solaris.
I created a table using your definition and put the data into a
file, converting ^I to tab and removing the trailing $.
perl -lne 'if (/^67829/) {s/\^I/\t/g; s/\$$//; print}' msg.txt data.txt
I used COPY to load data.txt; SELECT then returned the following:
\x
SELECT * FROM export_invoice;
...
kilos | 570.000
nett | 0.000
dimensions|
terms | CIF-MONTREAL/ACT 30-DAYS
bank |
goods | Chemist's sundries
...
I see a few COPY fixes in pgsql-committers, although I don't know
if any would affect the behavior you're seeing. Have you tried
8.0.4? Have you done any tests with a freshly-created table? Have
you executed any ALTER TABLE statements on export_invoice? I don't
know if COPY could have problems with that; I'm just wondering what
might be different between your environment and mine, aside from
the PostgreSQL version and operating system.
On another note, regarding the following:
invdate | date | not null default ('now'::text)::date
taxpoint | date | not null default ('now'::text)::date
Are you sure you want 'now'::text as a default, considering the
warning against it?
http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] Problem with COPY in 8.0.3
On Wed, Oct 12, 2005 at 07:08:20PM +0100, Oliver Elphick wrote: I should add that the table inherits from another one, but the swapped columns are a long way into the extra columns specific to this table. Could you post the table definitions? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1957: Error for createlang
On Wed, Oct 12, 2005 at 11:02:26AM +0100, Eduard Wulff wrote: createlang.exe -U postgres -e plperl template1 gives: createlang: Installation der Sprache fehlgeschlagen: ERROR: could not load library D:/dapps/PostgreSQL/8.1-beta2/lib/plperl.dll: Das angegebene Modul wurde nicht gefunden. same for tcl for python: createlang: Installation der Sprache fehlgeschlagen: ERROR: unsupported language plpython TIP: The supported languages are listed in the pg_pltemplate system catalog. plpython is listed there ... I don't know about the plperl or pltcl problems, but are you sure plpython is in pg_pltemplate? plpythonu should be there but not plpython, at least not in a distribution built from the unmodified source code. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] Problem with COPY in 8.0.3
On Wed, Oct 12, 2005 at 12:19:41PM -0600, Michael Fuhr wrote: On Wed, Oct 12, 2005 at 07:08:20PM +0100, Oliver Elphick wrote: I should add that the table inherits from another one, but the swapped columns are a long way into the extra columns specific to this table. Could you post the table definitions? BTW, I meant the CREATE TABLE statements, not the \d output. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Problem with COPY in 8.0.3
On Wed, Oct 12, 2005 at 08:23:15PM +0100, Oliver Elphick wrote:
On Wed, 2005-10-12 at 12:13 -0600, Michael Fuhr wrote:
On another note, regarding the following:
invdate | date | not null default
('now'::text)::date
taxpoint | date | not null default
('now'::text)::date
Are you sure you want 'now'::text as a default, considering the
warning against it?
I actually use CURRENT_DATE; that is what the system turns it into.
Ah yes, I see that now. I generally use now(), so I hadn't noticed
that CURRENT_DATE and CURRENT_TIMESTAMP become 'now', whereas only
a literal 'now' is expanded at create time:
CREATE TABLE foo (
d1 date NOT NULL DEFAULT now(),
d2 date NOT NULL DEFAULT CURRENT_DATE,
d3 date NOT NULL DEFAULT 'now'
);
\d foo
Table public.foo
Column | Type | Modifiers
+--+--
d1 | date | not null default now()
d2 | date | not null default ('now'::text)::date
d3 | date | not null default '2005-10-12'::date
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] Problem with COPY in 8.0.3
On Wed, Oct 12, 2005 at 08:17:23PM +0100, Oliver Elphick wrote: On Wed, Oct 12, 2005 at 12:19:41PM -0600, Michael Fuhr wrote: Could you post the table definitions? Here it is: I created the tables you posted (sans foreign key constraints because you didn't include the referenced tables) and loaded the data from your previous post into an 8.0.4 server on FreeBSD 4.11-STABLE. Here's what I got; it looks correct around the columns you report having trouble with: \x SELECT * FROM export_invoice; -[ RECORD 1 ]-+ invno | 67829 customer | 22058 account | X378 invdate | 2004-02-10 taxpoint | 2004-02-10 discount | 0.000 ordno | 5411 custref | 15498-00 currency | GBP carriage | 0.00 printed | t assigned | t customer_ean | location_ean | exchange_rate | grpid | postcode | grn | orderdate | 2004-02-10 packages | 60 packing | 0.00 other | 0.00 other_desc| 0 insurance | 0.00 pack_desc | Carton kilos | 570.000 nett | 0.000 dimensions| terms | CIF-MONTREAL/ACT 30-DAYS bank | goods | Chemist's sundries marks | AMG MEDICAL;5411;MONTREAL;1-60; port | THAMESPORT transport | SEA/ALLIANCE destination | CA origin| GB auth | MAIREAD BOYCE, DIRECTOR copies| 1 -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] Problem with COPY in 8.0.3
On Wed, Oct 12, 2005 at 07:28:37PM -0400, Tom Lane wrote:
Oliver Elphick olly@lfix.co.uk writes:
On Wed, 2005-10-12 at 17:45 -0400, Tom Lane wrote:
Hm. Could we see the actual pg_attribute data for both this table and
its parent?
Here you are:
Thanks. Nothing particularly strange-looking there though. Do you want
to try tracing through COPY with a debugger to see where it's getting
the bogus column order from?
Do you get the same bogosity with the attached test case? It's
based on the same table definition and data from your first message;
about the only differences from what you're doing are that the table
isn't inherited and it uses COPY foo FROM stdin instead of reading
the data from an external file. If this test works correctly, what
happens if you use COPY FROM stdin on the real table?
--
Michael Fuhr
CREATE TABLE foo (
invno integer not null,
customercharacter varying(10) not null,
account character varying(8)not null,
invdate datenot null default
('now'::text)::date,
taxpointdatenot null default
('now'::text)::date,
discountnumeric(5,3)not null default 0.0,
ordno integer not null,
custref textnot null default 'NONE'::text,
currencycharacter(3)not null,
carriagenumeric(10,2) not null default 0.00,
printed boolean not null default false,
assignedboolean not null default false,
customer_eancharacter varying(13) ,
location_eancharacter varying(13) ,
exchange_rate numeric(10,4) ,
grpid character varying(2) ,
postcodecharacter varying(10) ,
grn character varying(20) ,
orderdate date ,
packagesinteger not null default 1,
packing numeric(12,2) ,
other numeric(12,2) ,
other_desc text ,
insurance numeric(12,2) ,
pack_desc text ,
kilos numeric(12,3) ,
nettnumeric(12,3) ,
dimensions text ,
terms text ,
banktext ,
goods text ,
marks text ,
porttext ,
transport text ,
destination character(2) ,
origin character(2)not null default 'GB'::bpchar,
authtext ,
copies integer not null default 1
);
COPY foo FROM stdin;
67829 22058 X37810 FEB 2004 10 FEB 2004 0.000 5411
15498-00GBP 0.00t t \N \N \N \N
\N \N 10 FEB 2004 60 0.000.000 0.00Carton
570.000 0.000 CIF-MONTREAL/ACT 30-DAYSChemist\'s
sundries AMG MEDICAL;5411;MONTREAL;1-60; THAMESPORT SEA/ALLIANCECA
GB MAIREAD BOYCE, DIRECTOR 1
\.
SELECT * FROM foo;
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] .pgpass does not work for createlang
On Mon, Oct 10, 2005 at 06:25:59PM -0700, John Shin wrote: It seems that for v8.0.4 the createlang will not take .pgpass inputs and always prompts for password. Is this broken for the new version? Well, now I have to type in the password for my database script. -bash-3.00$ createdb test -h localhost -U postgres CREATE DATABASE Is that the exact command you ran? As shown it should fail with a syntax error because the options are in the wrong place: % createdb test -h localhost -U postgres createdb: too many command-line arguments (first is localhost) Try createdb --help for more information. -bash-3.00$ createlang plpgsql test -h localhost -U postgres Password: Likewise: % createlang plpgsql test -h localhost -U postgres createlang: too many command-line arguments (first is -h) Try createlang --help for more information. Use the --help option or consult the createlang documentation to see the correct order of options and arguments. It works fine here with .pgpass if the syntax is correct; have you run createlang --version to verify that you're running the 8.0.4 version? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] .pgpass does not work for createlang
[Please copy the mailing list on replies so others can participate in and learn from the discussion.] On Tue, Oct 11, 2005 at 11:16:40AM -0700, John Shin wrote: Tried many diffent ways but nothing seems to stick for createlang. .pgpass works for all others (createdb, dropdb, createuser, dropuser, etc.) [snip] -bash-3.00$ createdb test -h localhost -U postgres CREATE DATABASE Hmmm...this command fails with too many command-line arguments on both FreeBSD and Solaris (8.0.4 built from source on both), so I wonder if your build is using a getopt_long() that behaves differently than what I'm using. Might not matter, though. -bash-3.00$ createlang -h localhost -U postgres plpgsql test Password: Have you done a process trace (strace, ktrace, truss, etc.) to see if createlang is even looking for .pgpass? Does it work if you set the PGUSER environment variable instead of using the -U option? You mentioned FC4, so maybe Tom Lane or somebody more familiar with that platform has some ideas about what's happening. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #1938: pg_dump mis-intreprets default now();
On Tue, Oct 04, 2005 at 08:29:23PM +0100, Jeff MacDonald wrote: I did a backup from 7.3.2 using pg_dumpall. When I did a restore all of my timestamps that were defaulted to now(); were now defaulted to the time that I piped my dump back into postgres. Meaning the now() was parsed instead of just being copied. Are you sure the default was now() and not 'now'? If you still have the 7.3.2 server running then check the table definitions. Aside from using psql's \d commands, you could do this: SELECT adrelid::regclass, adnum, adsrc FROM pg_attrdef ORDER BY adrelid, adnum; The 7.3 documentation warns against using 'now', and the 7.4 Release Notes mention a behavior change with respect to column defaults. http://www.postgresql.org/docs/7.3/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT http://www.postgresql.org/docs/7.4/interactive/release-7-4.html -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1888: bug in cast from float4 to float8
On Sat, Sep 17, 2005 at 04:00:50AM +0100, Pablo Oses wrote: select cast(123.456::float4 as float8) what do you think its the answer ? 123.456 ? well pgsql 7.2.2, 8.0.0, 8.0.1 and 8.0.3 (all i have used) answers this: 123.456001281738 which is WRONG Float4 has a precision of only about 6 digits, so you can't assume much about the digits beyond that. See the documentation for floating-point types: http://www.postgresql.org/docs/8.0/interactive/datatype.html#DATATYPE-FLOAT This is hardly a behavior unique to PostgreSQL; use a search engine to find explanations of the problems of using binary floating-point numbers. If you need exact results then use the numeric type. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1885: SHOW autovacuum settings tab completion broken
On Fri, Sep 16, 2005 at 06:02:14AM +0100, Robert Treat wrote: PostgreSQL version: 8.1beta1 When doing SHOW tabtab or SHOW atabtab none of the autovacuum settings come up. Are you sure you're using an 8.1beta1 psql? I think tab completion were fixed to read the variable list from pg_settings before 8.1beta1 was released: http://archives.postgresql.org/pgsql-committers/2005-08/msg00151.php Here's what I get: test= SHOW atabtab add_missing_from authentication_timeout autovacuum_naptime autovacuum_vacuum_threshold all autovacuum autovacuum_vacuum_cost_delay archive_command autovacuum_analyze_scale_factor autovacuum_vacuum_cost_limit australian_timezones autovacuum_analyze_threshold autovacuum_vacuum_scale_factor -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1862: ECPG Connect, host variable trailing blanks
On Fri, Sep 16, 2005 at 12:00:59PM -0400, Bruce Momjian wrote: [EMAIL PROTECTED] wrote: What do you think of an Postgres option that would enable stripping of trailing blanks from connect host variables when turned ON? I would not support such an option. If we added flags for every single thing that someone wanted, the system would be unusable. [snip] Just because Oracle does it doesn't mean we should. Does Oracle really munge data on the client side? Or does it, like PostgreSQL, pass the host variable's value as-is to the server, and the server considers trailing spaces significant or not depending on the context? Is it the client-side behavior or the server-side behavior that's different between PostgreSQL and Oracle? If Oracle strips trailing spaces on the client side, is that a configurable option? How would you insert significant trailing spaces into a VARCHAR column if the client library strips them? -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #1870: Insertion problem
On Fri, Sep 09, 2005 at 08:54:05AM +0100, Sijin MS wrote: We are a Software Provider and promoting Linux. Most of our clients are using Linux. We are using postgres v7.3.2-3 as a backend for our application software. While inserting data into table, we found that sometime the data is not inserting into the table and also haven't raise any error messages. But if we do the same process again then it is inserting into the table properly. You haven't given us much to go on, but I'll point out that a number of bugs have been fixed since 7.3.2, some involving data loss. Those bugs aren't necessarily responsible for the behavior you're seeing, but you should consider upgrading nonetheless. If you must stay with 7.3 then consider upgrading to 7.3.10 (the latest). See the Release Notes for a summary of bug fixes and other changes: http://www.postgresql.org/docs/7.3/static/release.html Regarding your application: what language and API are you using? How do users interface with the application (custom GUI, web browser, etc.)? How are you checking for errors? Are you sure that the application *would* detect errors if they occurred (i.e., have you tested the error checking code by intentionally causing errors)? Do the server's logs show any error messages? How often does this happen? Can you duplicate the problem on demand? How long after the insert are you checking whether the data was inserted? How are you checking? Could the data have been inserted and then deleted? Might the inserting transaction have been rolled back? Do you have any triggers that might be silently discarding the insert? -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] BUG #1862: ECPG Connect, host variable trailing blanks
On Wed, Sep 07, 2005 at 12:06:10PM -0700, [EMAIL PROTECTED] wrote: According to the documentation at: http://www.postgresql.org/docs/8.0/static/ecpg-connect.html EXEC SQL CONNECT TO :target USER :user; The last form makes use of the variant referred to above as character variable reference. The complete text is: The last form makes use of the variant referred to above as character variable reference. You will see in later sections how C variables can be used in SQL statements when you prefix them with a colon. Note C variables -- it's talking about the C character type, not the SQL character type. To be consistent with other uses of host variables, we request that host variables within a connect statement act like host variables in DML statements. They *do* act the same way: the value is passed to the server, which handles it according to the appropriate type's semantics. If the host variable's value is used in a CHAR context then trailing spaces aren't significant in comparisons; if the value is used in other contexts like VARCHAR, TEXT, or NAME, then trailing spaces *are* significant. The server makes the decision, not the ECPG preprocessor or library, which simply passes the value to the server. If you think this behavior should be changed then propose it on pgsql-hackers and discuss it with the developers. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #1862: ECPG Connect, host variable trailing blanks
On Tue, Sep 06, 2005 at 09:02:47PM +0100, James Gray wrote: The problem that we are having involves a connect statement with host variables: EXEC SQL CONNECT TO :target AS :user Our problem is that we are passed Cobol strings which are blank padded. Our string strategy works fine for Oracle, but not for Postgres CONNECTs. For example, if we are trying to connect to: - database: demo - user: scott - password: tiger the strings must be demo, scott and tiger. With trailing blanks user scott will not match user scott , which is what we will present if the user had defined the Cobol variable as PIC X(10). In PostgreSQL, scott and scott are distinct identifiers, and both are valid. See Identifiers and Key Words in the SQL Syntax chapter of the documentation, especially the part that discusses quoted identifiers: http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS Although creating databases, users, tables, etc., with trailing spaces is probably a bad idea, PostgreSQL does allow such names, and the trailing spaces are significant. This only applies to CONNECT host variables, since trailing blanks in a CHAR column are ignored in comparisons for all other interactions with Postgres. Since this is inconsistent behavior, and also doesn't match Oracle's behavior, we are requesting a fix or an option. Identifiers are NAME types, not CHAR types; the difference in behavior is no more inconsistent than that between VARCHAR and CHAR. If the strings have trailing spaces but the identifiers on the server side don't, then strip the spaces on the client side. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] extra columns in intermediate nodes not being removed by top level of executor
On Sat, Sep 03, 2005 at 04:29:25PM -0400, Allan Wang wrote: I'm using 8.1 from CVS head of about two days ago. Extra columns seem to be on sum(plays.length), videos.path, videoid Here's a simplified, complete test case: CREATE TABLE foo ( x integer, y integer ); INSERT INTO foo (x, y) VALUES (1, 2); SELECT * FROM (SELECT sum(x), (SELECT y) AS yy FROM foo GROUP BY y) AS s LIMIT 1; sum | yy | -++--- 1 | 2 | 2 (1 row) SELECT * FROM (SELECT sum(x), (SELECT y) AS yy FROM foo GROUP BY yy) AS s LIMIT 1; sum | yy -+ 1 | 2 (1 row) SELECT * FROM (SELECT sum(x), (SELECT y) AS yy FROM foo GROUP BY y) AS s; sum | yy -+ 1 | 2 (1 row) SELECT * FROM (SELECT sum(x), y AS yy FROM foo GROUP BY y) AS s LIMIT 1; sum | yy -+ 1 | 2 (1 row) SELECT * FROM (SELECT x, (SELECT y) AS yy FROM foo) AS s LIMIT 1; x | yy ---+ 1 | 2 (1 row) -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1858: setting search path in select doesn't (always) work
On Fri, Sep 02, 2005 at 10:58:12AM -0400, Tom Lane wrote: D.J. Kniep [EMAIL PROTECTED] writes: After setting the search path the query Select * from vwexternetoegang produces the required results in the first installation, but in the new installation, it cannot find the view. However, if I do an explicit Set search_path to testschema; it works as expected. What does show search_path report in the failing and non-failing states? Also try select current_schemas(true). This thread came up in pgsql-general yesterday; Dick says the real problem was something else. http://archives.postgresql.org/pgsql-general/2005-09/msg00074.php -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1853: explain gives ERROR: bogus varno: 23
On Sun, Aug 28, 2005 at 06:13:12AM +0100, Dustin Sallings wrote: PostgreSQL version: 8.1b1 Description:explain gives ERROR: bogus varno: 23 This might be the same problem that was fixed yesterday: http://archives.postgresql.org/pgsql-committers/2005-08/msg00374.php Try building the latest code from CVS and see if you still get the error. Do you get the error if you execute SET enable_bitmapscan TO off; and then run EXPLAIN? -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1847: Error in some kind of UNION query.
On Thu, Aug 25, 2005 at 09:53:26AM +0100, THIBAULT Jean-Jacques wrote: this query work : select null UNION 1; Please show the *exact* query. The above produces a syntax error: test= select null UNION 1; ERROR: syntax error at or near 1 at character 19 LINE 1: select null UNION 1; ^ I suspect this is the actual query: test= select null UNION select 1; ?column? -- 1 (2 rows) this query doesn't work : select null UNION null UNION 1; ERROR: UNION types text and integer cannot be matched Again, the query as written causes a syntax error. This is probably the actual query: test= select null UNION select null UNION select 1; ERROR: UNION types text and integer cannot be matched To understand what's happening, see UNION, CASE, and ARRAY Type Resolution in the Type Conversion chapter of the documentation: http://www.postgresql.org/docs/8.0/static/typeconv-union-case.html -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] PQconnectdb SSL (sslmode)
On Thu, Aug 25, 2005 at 06:20:52PM -0700, vishal saberwal wrote: I am not sure if this is a bug. If you're not sure then it would be better to post to another mailing list like pgsql-general or one of the more specific lists. That would expose your question to a larger pool of expertise, and it would minimize the number of false bug reports. int ret=PQconnectdb(hostaddr=192.168.200.10 dbname=dbm user=postgres sslmode=prefer); ' ret' prints -1. PQconnectdb() returns a PGconn * -- why are you assigning the return value to an int? After PQconnectdb() you should be calling PQstatus() and checking its return value. If the status isn't CONNECTION_OK then PQerrorMessage() should return a string saying why. See the libpq documentation for more information: http://www.postgresql.org/docs/8.0/static/libpq.html#LIBPQ-CONNECT http://www.postgresql.org/docs/8.0/static/libpq-status.html -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #1831: plperl gives error after reconnect.
On Fri, Aug 19, 2005 at 11:16:25PM -0600, Michael Fuhr wrote: But this example crashes the backend if plperl.use_strict is enabled :-( The PL/Perl regression tests also fail if use_strict is enabled, mostly due to not using my in a few places. I'll work on a patch. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
