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] INSERT extremely slow with large data sets
There is a pgsql-performance list, which was created for questions like yours. Your problem was brought up many times before, so searching the archives is an alternative. Regards, Christoph > > Hi Everyone, > > This is my first post here so please tell me to go somewhere else if this > is the wrong place to post questions like this. > > I am using PostgreSQL 7.3.2 and have used earlier versions (7.1.x onwards) > and with all of them I noticed same problem with INSERTs when there is a > large data set. Just to so you guys can compare time it takes to insert > one row into a table when there are only few rows present and when there > are thousands: > > Rows Present Start Time Finish Time > > 100 1068790804.12 1068790804.12 > 1000 1068790807.87 1068790807.87 > 5000 1068790839.26 1068790839.27 > 1 1068790909.24 1068790909.26 > 2 1068791172.82 1068791172.85 > 3 1068791664.06 1068791664.09 > 4 1068792369.94 1068792370.0 > 5 1068793317.53 1068793317.6 > 6 1068794369.38 1068794369.47 > > As you can see if takes awfully lots of time for me just to have those > values inserted. Now to make a picture a bit clearer for you this table > has lots of information in there, about 25 columns. Also there are few > indexes that I created so that the process of selecting values from there > is faster which by the way works fine. Selecting anything takes under 5 > seconds. > > Any help would be greatly appreciated even pointing me in the right > direction where to ask this question. By the way I designed the database > this way as my application that uses PGSQL a lot during the execution so > there was a huge need for fast SELECTs. Our experiments are getting larger > and larger every day so fast inserts would be good as well. > > Just to note those times above are of INSERTs only. Nothing else done that > would be included in those times. Machine was also free and that was the > only process running all the time and the machine was Intel(R) Pentium(R) > 4 CPU 2.40GHz. > > Regards, > Slavisa > ---(end of broadcast)--- TIP 8: explain analyze is your friend
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 > > UPDATE"s. > > 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] 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] 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] 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.2>psql 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
>
> 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] 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] [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
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])
[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 ch 19446 19274 0 09:32:56 ttyp2 0:00 Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
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?
> > 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] 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?
> > 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] 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 ?
> > > 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 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]
Re: [HACKERS] REPEATED INSERT INTO ... 2nd thread
>
> On Mon, 2003-02-24 at 07:22, Christoph Haller wrote:
> > 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.
>
> What do you mean by "program variables"? If you're just referring to
> variables in the programming language the DB client is written in, I
see
> no reason why you couldn't use COPY to input those.
>
Interesting. Suppose you have a C application fragment like
for (CmdIndex = start_index; CmdIndex < nRows; CmdIndex++)
{
sprintf(CmdLine,
"INSERT INTO AArray_Values ( Primary_Key, List_Pointer,\
Parameter_Name, Parameter_Code,\
Dim_Pointer, File_Pointer, Source_Type )\
VALUES ( %d,%d,'%s',%d,%d,%d,'%s' );",
AArray_Values[CmdIndex].primary_key,
AArray_Values[CmdIndex].value_list_ptr,
AArray_Values[CmdIndex].parameter_name,
AArray_Values[CmdIndex].parameter_code,
AArray_Values[CmdIndex].dim_pointer,
AArray_Values[CmdIndex].file_pointer,
AArray_Values[CmdIndex].source_type);
/*
call PQexec() to process
*/
}
How would you replace this by COPY?
Hoping I'm not partially out of order but I cannot see how to achieve
that.
Thanks for your time.
Regards, Christoph
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
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
[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 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])
[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])
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
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] 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
[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 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]
