Re: [HACKERS] executing prepared select, missing RowDescription info
- Begin Forwarded Message - From To:[EMAIL PROTECTED] Mon Apr 19 11:08:57 2004 Date: Mon, 19 Apr 2004 11:08:57 METDST To: [EMAIL PROTECTED] (Tom Lane) Subject: Re: [HACKERS] executing prepared select, missing RowDescription info In-Reply-To: [EMAIL PROTECTED]; from Tom Lane at Apr 18, 104 7:11 pm Content-Length: 899 Status: RO Kris Jurka [EMAIL PROTECTED] writes: When executing a prepared select statement, the returned RowDescription protocol message does not have any information for the table oid or column position. Running the equivalent select without prepare provides this information, so I don't see why the act of preparing and executing the statement removes this valuable data. Any insight on why it isn't there or how to fix it? Fixing this would be a tad messy, because the information is not propagated up through a utility-statement Portal. I guess I would ask why you're using EXECUTE at all; it's considerably less efficient than invoking the prepared statement via the protocol-level operation for doing so (Bind, then Execute). regards, tom lane And how would I do this more efficient Bind, then Execute using libpq? TIA Regards, Christoph - End Forwarded Message - ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Prepared select
How can I use a prepared select statement as mentioned in the documentation= on SQL PREPARE. Preparing the statement is easy, the problem is using the = plan to get a cursor. My assumption is the SQL OPEN command is not document= ed or there is some other libpq API to make this happen. Thanks I'm using libpq and lines like below are working: res = PQexec(conn, PREPARE plan001 ( integer , double precision , character ) AS SELECT a,b,d FROM foo WHERE a = $1 OR d $2 OR b = $3); ... res = PQexec(conn, EXECUTE plan001 ( 3 , 6.66 , 'whatever' ) ); HTH, pretty late reply - I know (but no one else did as far as I can tell) Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Disaster!
Tom Lane wrote: I said: If there wasn't disk space enough to hold the clog page, the checkpoint attempt should have failed. So it may be that allowing a short read in slru.c would be patching the symptom of a bug that is really elsewhere. After more staring at the code, I have a theory. SlruPhysicalWritePage and SlruPhysicalReadPage are coded on the assumption that close() can never return any interesting failure. However, it now occurs to me that there are some filesystem implementations wherein ENOSPC could be returned at close() rather than the preceding write(). (For instance, the HPUX man page for close() states that this never happens on local filesystems but can happen on NFS.) So it'd be possible for SlruPhysicalWritePage to think it had successfully written a page when it hadn't. This would allow a checkpoint to complete :-( Chris, what's your platform exactly, and what kind of filesystem are you storing pg_clog on? We already have a TODO on fclose(): * Add checks for fclose() failure Tom was referring to close(), not fclose(). I once had an awful time searching for a memory leak caused by a typo using close instead of fclose. So adding checks for both is probably a good idea. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] *sigh*
Fairly good idea IMHO, especially considering Christopher's point about the unlikeliness of needing an exact count anyway. Regards, Christoph How about: Implement a function estimated_count that can be used instead of count. It could use something like the algorithm in src/backend/commands/analyze.c to get a reasonably accurate psuedo count quickly. The advantage of this approach is that count still means (exact)count (for your xact snapshot anyway). Then the situation becomes: Want a fast count? - use estimated_count(*) Want an exact count - use count(*) regards Mark Christopher Browne wrote: For a small table, it will be cheaper to walk through and calculate count(*) directly from the tuples themselves. The situation where it may be worthwhile to do this is a table which is rather large (thus count(*) is expensive) where there is some special reason to truly care how many rows there are in the table. For _most_ tables, it seems unlikely that this will be true. For _most_ tables, it is absolutely not worth the cost of tracking the information. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [pgsql-advocacy] Not 7.5, but 8.0 ?
Joshua D. Drake wrote: Hello, If Win32 actually makes it into 7.5 then yes I believe 8.0 would be appropriate. It might be interesting to track Oracle's version number viz. its feature list. IOW, a PostgreSQL 8.0 database would be feature equivalent to an Oracle 8.0 database. That would mean: 1) PITR 2) Distributed Tx 3) Replication 4) Nested Tx 5) PL/SQL Exception Handling IMHO, a major version number jump should at least match the delta in features one finds in the commercial segment with their major version number bumps. Otherwise, I suspect it would be viewed as window dressing... Good point. To me the best argument against so far. Could be wrong, though... Mike Mascari [EMAIL PROTECTED] Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] An interisting conundrum where tables have a column called found
I am putting together a DB that records information about a set of web
sites and how they link to one another. As one site refers to another, I
monitor the first site and then record when I find the referred site.
[snip]
I also have a function called add_site that adds the newly found site.
So far so good.
To test my code I wrote the INSERT statement by hand:
insert into sa_site (site_id, found, host_uri) values
(nextval('sa_site_id_seq'), 'now', 'www.endoid.net');
and everything worked fine when called from psql.
Then I added the code to my add_site function and got the following
error:
ensa1.1= select add_site('www.endoid.net', 4, null );
WARNING: Error occurred while executing PL/pgSQL function add_site
WARNING: line 26 at SQL statement
ERROR: parser: parse error at or near $1 at character 43
I looked and looked but couldn't find anything that could explain the
error. Then, being somewhat used to Oracle I tried renaming the found
column to found_on. Oracle occasionally has discrepencies in its rules
for the naming of objects, so I thought that something *similar* might
be happening with PG. Anyways this change did work in my PL/pgSQL
function.
Could you guys figure out where a general description of please don't
use keywords as column names even if you're allowed to at create time
because something somewhere will throw an unintellligable error should
live on the site?
There is a SQL Key Words section, and I remember when porting to
postgres I saw complaints about a column named 'offset'.
So I assume there is a key word checking function already in operation.
Maybe it simply needs an update.
Regards, Christoph
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Automatic compat checking? (was 7.4 compatibility question)
On Wednesday 22 October 2003 07:37, Neil Conway wrote: The second audience is the people who are really interested in exactly what has changed between the new release of PostgreSQL and the previous release series. It is important that we make it easy for an admin planning a PostgreSQL upgrade at a fairly large site to be able to see what changes in PostgreSQL have been made, and what changes will be necessary in their own applications. Something I was pondering the other day was whether a pg_compat_chk utility would be practical/desirable. You run it against your existing database / schema dump and it prints a set of warnings: Old version = 7.2.1 New version = 7.4.0 Warning: schema support introduced (v7.3) all objects will be placed in the default schema Failure: DEFAULT 'now' not supported (v7.4) table1.column2 table2.column3 Notice: timestamp now holds milliseconds by default (v7.3) tableX.whatever My main concern would be that a 90% solution might be worse than nothing at all. Incidentally, this is not idle speculation, but something I might well have time to stick in gborg during the 7.5 devt cycle. -- Richard Huxton Archonet Ltd A pg_compat_chk utility sounds great. No idea, if this is practical, but it's desirable - at least to me. Regards, Christoph PS I'm surprised no one else replied. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] change of table name - any help
We have a development server running OS - Linux development-server 2.4.20-openmosix-r4 #1 SMP Mon May 19 02:32:52 PDT 2003 i686 Intel(R) Xeon(TM) CPU 2.40GHz GenuineIntel GNU/Linux Database - PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.1 We have a table ctcert_name under postgres DB(postgres schema and postgres user is the owner). Suddenly, this object started missing from the DB (I am the only Person who connects to that server and did not drop/renamed it). When I tried to recreate the same table, the system threw me back an error, saying that postgres.ctcert_name already exists. I am neither able to drop or rename the table. checked the DB logs and there is no drop/rename table statement in that. I have the transaction logs, but not able to read, as they are not in the human readable format. How can I decipher from the txn logs, if it captures the change management. Can somebody please tell me, what cud have gone wrong and is the error is reproduceable? What is the solution for this kind of problem. Did you change the SEARCH_PATH variable? Did I get this right: You cannot DROP TABLE postgres.ctcert_name ; Mind, I left off the enclosing quotes. And you cannot CREATE TABLE postgres.ctcert_name( ... ) ; My suspicion is you are using these quotes and you shouldn't. Regards, Christoph ---(end of broadcast)--- TIP 3: 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
[HACKERS] Copyright (C) 1996-2002
Today I've d-loaded PostgreSQL 7.3.4. I've seen in $PGSQLD/doc/html/index.html it still says Copyright (C) 1996-2002 shouldn't it be 2003? Regards, Christoph PS I've sent this to [EMAIL PROTECTED] before. But in return I've got Your message to pgsql-docs has been delayed, and requires the approval of the moderators, for the following reason(s): The author (Christoph Haller [EMAIL PROTECTED]) is not a member of any of the restrict_post groups. ---(end of broadcast)--- TIP 3: 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: [HACKERS] no of affected rows in prepared stmts
hi whenever i call an execute on a prepared statement, i get the return value of PQcmdTuples() as NULL even if the query did modify tuples... how can i get the number of affected tuples? thanx in adv. rahul I'm observing the same pretty odd behavior. Do we both expect something wrong. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
Paulo Scardine wrote: LockAcquire has a dontWait parameter, which do just what I want. The executor level calls heap_open(relid, RowShareLock) when doing FOR UPDATEs. Should we define something like RowShareLockNoWait, so heap_open() or other lower level functions can call LockAcquire() with dontWait set? By the way, is this kind of question on-topic for pgsql-hackers? I think there are two issues with implementing nowait locking: If we have special syntax for FOR UPDATE, we will need it for other commands that need no wait behavior, and after a while they all carry around that cruft --- SET seems easier and more useful. Second, I don't think we want to carry around a NOWAIT boolean in all our structures --- a SET would control it easier. The SET can be checked right in the lock code, and I think having it control only exclusive locks would do almost everything we want. Sounds reasonable to me. You'll have my vote for the SET way. Regards, Christoph ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] SELECT FOR UPDATE NOWAIT
Rod Taylor wrote: -- Start of PGP signed section. On Fri, 2003-07-18 at 19:46, Paulo Scardine wrote: My boss is asking for something like Oracle's SELECT FOR UPDATE NOWAIT. Is there any such feature? If no, should I look forward into implementing this? Any advice? Lookup STATEMENT_TIMEOUT and set it to a very short time. Some people have said they want to distinguish between a slow query (busy system) and waiting on a lock. I can particulary see wanting to do a NOWAIT only on exclusive locks --- not sure how many really want that, though. I think I'm a quite attentive to the SQL and HACKERS list, and I see requests for a NOWAIT option at least once a month, and it's growing. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Exception table ...
I have just seen a nice feature provided by DB2 which seems very useful to me. When importing huge amounts of data (dozens of gigs) with the help of COPY errors might occur from time to time (especially when migrating). The problem with COPY is that it stops after the first error. So if the first problem occurs after 200.000.000 records it is somehow annoying to do the entire stuff again. If we had an option telling COPY to log all problems into a logtable or into a separate logfile we could finish the import and rollback the transaction after trying to import everything. This would help a lot when migrating or importing a lot of data because all problems with an import could be fixed at once based on the exception table. Did anybody think about a feature like that in the past? Does it make sense to the group? The same goes for me, sounds very useful. And if I didn't dream it, I'm pretty sure there have been requests for a feature like that before. Regards, Christoph ---(end of broadcast)--- TIP 3: 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: [HACKERS] FROM clause omitted
I had a bug in one of my queries that wasn't detected by pg because if filled in the from clause by itself. Take for example a querie like select foo.a; which I guess is transformed to select foo.a from foo; Is this really a good thing to do? Is it part of the standard? Can it be turned of? In my case it hid a bug and made my query work but produce the wrong result. Isn't this yet another case of helpful parsing that will only hurt in the end? Look at how hard it is to parse html-pages because all browsers accept broken code, but different broken code. What about an example like this (the transformed code above but with alias x added): select foo.a from foo x; By adding the alias x the query still workes but gives a different result. Dennis, This feature has been addressed many times before. Please search the archives and refer to Extensions within SELECT doc. AFAIK it can even be turned off, but don't know how exactly. As I said, search the archive. HTH Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] can't create database
on one of the AIX4.3.3, the 7.1.3 pgsql is installed by root on the system, then I tried to install 7.3.1/or 7.3.2 under another non-root user, I can run make, make check, make install, postmaster can start without errors, but when I try to createdb, here're some errors - createdb emrxdbs ERROR: 'autocommit' is not a valid option name createdb: database creation failed then I issued, postgre7.3.2psql template1 ERROR: parser: parse error at or near . Welcome to psql 7.3.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1= select version(); version PostgreSQL 7.1.3 on powerpc-ibm-aix4.3.3.0, compiled by cc (1 row) template1= it picks the older version and always having a parser error!! Any hints? You'll probably need to set a second PGPORT to make two versions run. See the docs. Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] timestamp/date in ecpg
I started working on date/timestamp in ecpg. So far I can read date types from the DB and I can insert date into the DB. However there seems to be a bug in converting timestamp to ascii or vice versa. If anyone of you knows more about timestamp2tm etc. could you please have a look at function PGTYPEStimestamp_ttoa in pgtypeslib/timestamp.c? Hmm, maybe the transformation in the other direction is the culprit. What I do is call ts1 = PGTYPEStimestamp_atot(2000-7-12 17:34:29, NULL); followed by a text = PGTYPEStimestamp_ttoa (ts1); Needless to say the resulting text is not 2000-7-12 17:34:29. :-( Maybe some of you have an idea. I am willing to have a look at the functions, but am failing in finding directory pgtypeslib/ within $PGSQLD (version 7.3.2). Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] timestamp/date in ecpg
Hmm, maybe the transformation in the other direction is the culprit. What I do is call ts1 = PGTYPEStimestamp_atot(2000-7-12 17:34:29, NULL); followed by a text = PGTYPEStimestamp_ttoa (ts1); Needless to say the resulting text is not 2000-7-12 17:34:29. :-( I could not dig too deep into the code until now, but isn't there a leading zero missing 2000-07-12 17:34:29 Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [SQL] What's wrong with this group by clause?
On Tue, 4 Nov 2003 18:28:12 -0300, Franco Bruno Borghesi
[EMAIL PROTECTED] wrote:
Below you can find a simplified example of a real case.
I don't understand why I'm getting the john record twice.
ISTM you have found a Postgres 7.3 bug.
I get one john with
PostgreSQL 7.1.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
and
PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
but two johns with
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
/*EXAMPLE*/
CREATE TABLE people
(
name TEXT
);
INSERT INTO people VALUES ('john');
INSERT INTO people VALUES ('john');
INSERT INTO people VALUES ('pete');
INSERT INTO people VALUES ('pete');
INSERT INTO people VALUES ('ernest');
INSERT INTO people VALUES ('john');
SELECT
0 AS field1,
0 AS field2,
name
FROM
people
GROUP BY
field1,
field2,
name;
field1 | field2 | name
++
0 | 0 | john
0 | 0 | pete
0 | 0 | ernest
0 | 0 | john
(4 rows)
PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
SELECT 0 AS field1, 0 AS field2,name FROM people GROUP BY
field1, field2, name;
field1 | field2 | name
++
0 | 0 | ernest
0 | 0 | john
0 | 0 | pete
(3 rows)
PostgreSQL 7.3.2 on hppa-hp-hpux10.20, compiled by GCC 2.95.2
SELECT 0 AS field1, 0 AS field2,name FROM people GROUP BY
field1, field2, name;
field1 | field2 | name
++
0 | 0 | john
0 | 0 | pete
0 | 0 | john
0 | 0 | pete
0 | 0 | john
0 | 0 | ernest
(6 rows)
I doubt this is a bug in 7.3.2 but in prior versions.
I've cross-checked how another DBMS (HP's ALLBASE) handles GROUP BY
without an aggregate, and it acts like 7.3.2.
Regards, Christoph
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] gmake -C regress check failure
Maybe this is related to the thread [HACKERS] regression failure in CVS HEAD I've installed postgresql-7.3.2 on HP-UX yesterday. When running 'gmake -C regress check' the process does not return. File ./src/test/regress/regression.out shows parallel group (13 tests): float8 int2 varchar text float4 int8 int4 name oid char boolean bit numeric boolean ... ok char ... ok name ... ok varchar ... ok text ... ok int2 ... ok int4 ... ok int8 ... ok oid ... ok float4 ... ok float8 ... ok bit ... ok numeric ... ok test strings ... ok test numerology ... ok parallel group (20 tests): point lseg ps (filtered) shows ch 16635 16492 0 09:29:13 ttyp2 0:00 gmake check ch 16763 16635 0 09:29:23 ttyp2 0:00 gmake -C src/test check ch 16764 16763 0 09:29:24 ttyp2 0:00 gmake -C regress check ch 16769 16764 0 09:29:24 ttyp2 0:00 /bin/sh ./pg_regress --temp-install --top-builddir=../../.. ch 19276 16769 0 09:31:31 ttyp2 0:00 tee ./regression.out ch 19274 16769 173 09:31:31 ttyp231:45 /bin/sh ./pg_regress --temp-install --top-builddir=../../.. ch 19443 19274 1 09:32:56 ttyp2 0:00 defunct ch 19446 19274 0 09:32:56 ttyp2 0:00 defunct Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] gmake -C regress check failure
Christoph Haller [EMAIL PROTECTED] writes: I've installed postgresql-7.3.2 on HP-UX yesterday. When running 'gmake -C regress check' the process does not return. See doc/FAQ_HPUX: : The parallel regression test script (gmake check) is known to lock up : when run under HP's Bourne shells: /usr/bin/sh and /sbin/sh. This is a : known defect JAGad84609, the fix for which is not yet in any released : HP-UX version or shell patches. To work around it, use ksh to run the : regression script: : gmake SHELL=/bin/ksh check : : If you see that the tests have stopped making progress and only a shell : process is consuming CPU, kill the shell process and start over with the : above command. Thanks. 'gmake SHELL=/bin/ksh check' now reports All 89 tests passed. Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] talking to postgresql from C/C++
Have you seen libpq - C Library Functions Associated with the COPY Command This is best way to INSERT large amounts of data. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Error codes revisited
Given the repeatedly-asked-for functionalities (like error codes) for which the stopper has been the long-threatened protocol revision, I'd think it might be boring, but would hardly be thankless. Heck, I'd expect a few whoops of joy around the lists. Yes. Error codes would be great. Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Can pessimistic locking be emulated?
Just as a suggestion: In most of my applications, we have a security layer which is implemented through server-side functions. These functions keep a table updated which contains: lock_table record_id lock_user time_locked That's an excellent and even portable idea. This allows us to avoid nasty your update cannot be processed-type error messages by showing the user up front which records are locked, as well as allowing the admin to decide when locks should time out. I tend to find in general that database locking mechanisms are a very poor locking strategy for a good UI. True. But you circumvented it elegantly. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Can pessimistic locking be emulated?
I am trying to emulate a pessimistic locking system you would find in an old school database file system, for example cobol. Generally, when a cobol program tries to read a record that is locked by somebody else, the read fails and either a message is displayed by the user or a error handling procedure is executed. I would like to emulate this behavior for legacy code while using mvcc for newer procedures I write. 4 questions: 1. Can you query if a tuple is locked by another transaction (the documentation unclearly suggests this can't be done via the pg_lock view) before executing select for update...? 2. If so, is this reasonable efficient to do, i.e. straight join on oid/xid? 3. If so, is this possible to query without a race condition regarding the lock status? 4. If so, is this likely to be possible in future versions of postgres without non-trivial changes? In other words, if User B attempts to select for update a record that user A has selected for update, it would be nice if User B's query would fail with a NOTICE to act upon. No idea if this is of any help, but you may have a look into PostgreSQL 7.3 Documentation 3.4. Run-time Configuration STATEMENT_TIMEOUT (integer) Aborts any statement that takes over the specified number of milliseconds. A value of zero turns off the timer. Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] numeric datataypes as seperate library
Michael Meskes kirjutas K, 26.02.2003 kell 13:00: Did anyone ever think about creating a library that is able to handle our numeric datatype? I'm currently thinking about adding this datatype among others to the ones know to ecpg so no one is forced to convert them or work on the strings. On the other hand I'm not sure if anyone's interested in this feature as you could always keep the numbers as strings and let the database do all calculation stuff. But then you cannot use the datatype in C. I see at least 3 datatypes that would be nice to have libraries for using in client programs - NUMERIC, VARBIT and our DATE/TIME/TIMESTAMP. Me too. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Can pessimistic locking be emulated?
That's my fallback position. Obviously, this will lead to false positives depending on server load. In my case, I'm targeting between 30-50 users so its likely to throw timeouts for various reasons other than locks even though my queries of interest are generally select a from b where id =3D c type of thing. This is a kludgy solution but its still better than writing cobol. The bigger issue is that a timeout will not return the reason the query timed out. There are cases where I would like to run a select for update over a range of records and handle the locked records and unlocked records differently. A query that could match locked oids vs the oids I am interested in would be super. I could then aggregate my select for updates into larger queries and reap massive performance gains. Another way of putting it is this: waiting for your select to timeout is kind of like parking in Manhattan: you back your car up until you hit the next car. I would sort of like to, uh, look in the rear view mirror first. I see your point. 1. Can you query if a tuple is locked by another transaction (the documentation unclearly suggests this can't be done via the pg_lock view) before executing select for update...? Where did you find this? Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] REPEATED INSERT INTO ...
The key word REPEATED directs INGRES to encode the INSERT and save its execution plan when it is first executed. This encoding can account for significant performance improvements on subsequent executions of the same INSERT. What do you others think of it? You can do that today with PREPARE/EXECUTE; there's no need to invent specialized syntax for it. Excellent. As often, a closer look into the documentation would have saved Tom's time. Nevertheless, I think the PREPARE/EXECUTE functionality could definitely use some more promotion, especially under Performance Tips. Thanks for your quick reply. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] I cant find it or I'm just lazy ?
How can I get information is TRANSACTION already started ? I did not mean 'TRANSACTION ISOLATION LEVEL', but 'TRANSACTION LEVEL' ! OK, it is bad construction - my fault ! What I meant is : IS-TRANSACTION-ALREADY-STARTED ? I used 'TRANSACTION LEVEL' because I saw that Bruce is working on nested transactions, so in future there could be more than one transaction started ? I could use something like IS-TRANSACTION-ALREADY-STARTED too, but AFAIK there is no such thing. Correct me if I am wrong, please. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] I cant find it or I'm just lazy ?
On Tuesday 25 February 2003 09:28, Christoph Haller wrote: On Mon, Feb 24, 2003 at 07:53:05PM +, Darko Prenosil wrote: I need two answers I did not find in documentation : How can I get exact number of rows in DECLARED CURSOR ? OK, I can FETCH until NULL, but this does not fits my needs ! You may want to use FETCH ALL, otherwise what or your needs in detail? If I use FETCH ALL all, all the data will be sent to client, then why to use CURSOR at all ? I need to reduce network trafic on slow connections ! I cannot see how you are going to reduce network traffic by knowing in advance how many rows will be returned. Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT() function on the query to learn about the number of rows to be returned. Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] I cant find it or I'm just lazy ?
On Mon, Feb 24, 2003 at 07:53:05PM +, Darko Prenosil wrote:
I need two answers I did not find in documentation :
How can I get exact number of rows in DECLARED CURSOR ?
OK, I can FETCH until NULL, but this does not fits my needs !
You may want to use FETCH ALL, otherwise what or your needs in detail?
You need to move to the end of the cursor. When you declare a cursor
it
doesn't run the query yet. You have to tell it to run the query before
it
can tell you how many rows it is. I think the command is MOVE.
But how could one MOVE to the last row?
How can I get information is TRANSACTION already started ?
(TRANSACTION LEVEL)
Either
SHOW TRANSACTION ISOLATION LEVEL ;
NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
SHOW VARIABLE
or
select current_setting('TRANSACTION ISOLATION LEVEL');
Regards, Christoph
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] REPEATED INSERT INTO ...
I've noticed subsequent executions of the same insert command are slow. I've searched the list archives for this matter and found several entries related, including suggestions how to speed up. The standard answer from the core team is, use COPY. Sorry, but this is from an application point of view not really an option if you're dealing with program variables. We used to have an INGRES installation around, and since I know Postgres is based on it, may be this old INGRES feature is worth to consider: Taken from the Reference Manual [REPEATED] INSERT INTO ... The key word REPEATED directs INGRES to encode the INSERT and save its execution plan when it is first executed. This encoding can account for significant performance improvements on subsequent executions of the same INSERT. What do you others think of it? Regards, Christoph ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] ecpg vs. libpq
I am wondering if there is any difference in performance between using ecpg and libpq. If I understand the concept of ecpg correctly, calls to the lecpg interface are internally converted to calls to libpq. So there is no big difference at all. Is this right? Regards, Christoph ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] ecpg vs. libpq
I am wondering if there is a fundamental difference in performance between using embedded SQL or libpq functions in a C application. If I understand the documentation correctly, calls to lecpg are simply transferred to calls to libpq. So, the difference in performance is, if any, marginal. Is this right? Regards, Christoph PS Sorry if this comes twice, the local mail host seems to be in trouble. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Group by, count, order by and limit
Consider this query on a large table with lots of different IDs: SELECT id FROM my_table GROUP BY id ORDER BY count(id) LIMIT 10; It has an index on id. Obviously, the index helps to evaluate count(id) for a given value of id, but count()s for all the `id's should be evaluated, so sort() will take most of the time. Is there a way to improve performance of this query? If not, please give some indication to do a workaround on the source itself, so perhaps I may be able to come out with a patch. Is there a difference in performance if you re-write it as SELECT id, count(id) FROM my_table GROUP BY id ORDER BY 2 LIMIT 10 ; ? Regards, Christoph ---(end of broadcast)--- TIP 3: 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: [HACKERS] pg environment? metadata?
I was wondering what kind of functions/constants exist in Postgre to dig up metadata. I barely scratched the surface of Oracle but I know you find things like user_tables there that can be used to extract info about your tables. What I'm looking for is some kind of functions to extract column names, possibly data types, etc. And by that I don't mean console commands, sql statements that will do the job with tcp/ip. Moreover, are there any ANSI standards for this kind of thing? Or each one to his own? Refer to the System Catalogs chapter within the Developer's Guide section of the documentation. In addition, if you start a psql session with the -E option, you will see how all these \d commands are generated. I would love to hear there is a standard about system catalogs, but I've never heard of one and I doubt there will be one ever in the future. Regards, Christoph ---(end of broadcast)--- TIP 3: 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
[HACKERS] IpcSemaphoreKill: ...) failed: Invalid argument
Hi, I've seen this (see below) in the postmaster's log-file. I doubt this is normal behaviour. I'm using PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2 Does anybody know what may cause calls to semctl resp. shmctl (semaphore control resp. shared memory control) to fail? The application program ( C code using the libpq - C Library ) crashed because of a segmentation violation. I've searched the archive for ZeroProcSemaphore IpcSemaphoreKill IpcMemoryDelete with no results. Any hints welcome. Thanks in advance. Regards, Christoph DEBUG: database system is ready NOTICE: COMMIT: no transaction in progress cut NOTICE: COMMIT: no transaction in progress DEBUG: pq_recvbuf: unexpected EOF on client connection DEBUG: pq_recvbuf: unexpected EOF on client connection ZeroProcSemaphore: semctl(id=2450,SETVAL) failed: Invalid argument DEBUG: server process (pid 10237) exited with exit code 255 DEBUG: terminating any other active server processes DEBUG: all server processes terminated; reinitializing shared memory and semaphores IpcSemaphoreKill: semctl(707088, 0, IPC_RMID, ...) failed: Invalid argument IpcSemaphoreKill: semctl(2449, 0, IPC_RMID, ...) failed: Invalid argument IpcSemaphoreKill: semctl(2450, 0, IPC_RMID, ...) failed: Invalid argument IpcMemoryDelete: shmctl(312410, 0, 0) failed: Invalid argument DEBUG: database system was interrupted at 2003-02-17 11:22:36 MET DEBUG: checkpoint record is at 0/47EA788 DEBUG: redo record is at 0/47EA788; undo record is at 0/0; shutdown TRUE DEBUG: next transaction id: 16242; next oid: 368814 DEBUG: database system was not properly shut down; automatic recovery in progress DEBUG: redo starts at 0/47EA7C8 DEBUG: ReadRecord: record with zero length at 0/48864B8 DEBUG: redo done at 0/4886490 DEBUG: database system is ready ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] IpcSemaphoreKill: ...) failed: Invalid argument
This is a fairly spectacular failure :-(. As far as I can see from the semctl and shmctl man pages, the only plausible reason for EINVAL is that something had deleted the semaphores and shared memory out from under Postgres. I do not believe that Postgres itself could have done that --- it had to be some external agency. Unless the kernel is broken, whatever requested those deletions had to be running as root or as postgres in order to have the necessary permissions. You sure you didn't have some loose-cannon script running around issuing ipcrm commands? No, I'm not sure at all about a loose-cannon script running around issuing ipcrm commands. I have to ask the other staff members what scripts are running. I already had a suspicion that something like an ipcrm command is causing this, but it was denied. Now, with your support they probably will believe me. Thanks for the quick reply. Regards, Christoph ---(end of broadcast)--- TIP 3: 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
