[HACKERS] php-psql lock problem. Thanks!
Hello to everybody! I've a little problem with LOCK-ing a certain row in a table using PHP and PostgreSQL on LINUX. In a few words, I'd like to undertand how find out if a certain row is locked, in order to prevent a kind of deadlock. Which is the (system) table where all locked row or tables are 'saved' ? Is there any flag? // Example 1: User A: BEGIN WORK; select login from people where userid='1' for update; [ ... ] COMMIT WORK; User B: BEGIN WORK; (***) select login from people where userid='1' for update; [ WAIT UNTIL 'COMMIT WORK' of user A ! :( ] COMMIT WORK; Solution: I'd like to put in (***) a quick check in order to know if the row with userid='1' is already locked or not. In this way, if it's already locked, I'll use select login from people where userid='1'; [ ONLY READ ] instead of select login from people where userid='1' for update; [READ WRITE] // Example 2: BEGIN WORK; LOCK TABLE utenti IN SHARE ROW EXCLUSIVE MODE; select login from people where userid='1'; COMMIT WORK; // Many thanks to everybody! Ciao! MaURIZIO [EMAIL PROTECTED] It's sure that a small example in PHP will very very appreciated!! :)) PS: it's possible to setup a timeout for a locked table, in order to exec an aoutomatic ROLLBACK ?? (for examples if the user goes away? *** ** Happy surfing on THE NET !! ** ** Ciao by ** ** C R I X 98 ** *** AntiSpam: rimuovere il trattino basso dall'indirizzo per scrivermi... (delete the underscore from the e-mail address to reply) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Feature Request - PL/PgSQL
Hi all, It would be very nice if PL/PgSQL could return a record set (ie, set of tuples). This could be done in two ways as far as I can imagine: either PL/PgSQL just returns the rows as a normal query would or it could return a cursor. The prior would be very useful, the latter easier to implement (especially if INOUT arguments get implemented =)). Currently, this seems to go against the grain of PL/PgSQL - am I missing something? Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Feature Request - PL/PgSQL
You already can return a cursor. Support for returning a record set is being worked on. -alex On Sun, 14 Oct 2001, Gavin Sherry wrote: Hi all, It would be very nice if PL/PgSQL could return a record set (ie, set of tuples). This could be done in two ways as far as I can imagine: either PL/PgSQL just returns the rows as a normal query would or it could return a cursor. The prior would be very useful, the latter easier to implement (especially if INOUT arguments get implemented =)). Currently, this seems to go against the grain of PL/PgSQL - am I missing something? Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FAQ error
Bruce Momjian writes:
Bruce Momjian writes:
OK, new FAQ code is:
$sql = SELECT nextval('person_id_seq');
$newSerialID = ($conn-selectrow_array($sql))[0];
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
$res = $dbh-do($sql);
This code is still incorrect for any known programming language and it's
even less clear to a person that doesn't know the programming language
it's probably trying to imitate.
OK, what suggestions do you have?
I didn't have a problem with the original version. It conveyed clearly
(to me), read the nextval and insert it yourself.
Obviously, someone did because they tried the code and it didn't work.
At least the new code is closer to valid, though less clear. It is at
least a valid snippet, which the previous version was not.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] FAQ error
Obviously, someone did because they tried the code and it didn't work.
At least the new code is closer to valid, though less clear. It is at
least a valid snippet, which the previous version was not.
OK, I changed it to more pseudocode:
new_id = output of SELECT nextval('person_id_seq')
INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal');
and
INSERT INTO person (name) VALUES ('Blaise Pascal');
new_id = output of SELECT currval('person_id_seq');
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(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_client_encoding
* Tatsuo Ishii [EMAIL PROTECTED] [011014 16:05]: ASCII SQL_ASCII UTF-8 UNICODE UTF_8 MULE-INTERNAL MULE_INTERNAL ISO-8859-1 LATIN1 ISO_8859_1 ISO-8859-2 LATIN2 ISO_8859_2 ISO-8859-3 LATIN3 ISO_8859_3 ISO-8859-4 LATIN4 ISO_8859_4 ISO-8859-5 ISO_8859_5 ISO-8859-6 ISO_8859_6 ISO-8859-7 ISO_8859_7 ISO-8859-8 ISO_8859_8 ISO-8859-9 LATIN5 ISO_8859_9 ISO-8859-10 ISO_8859_10 LATIN6 ISO-8859-13 ISO_8859_13 LATIN7 ISO-8859-14 ISO_8859_14 LATIN8 ISO-8859-15 ISO_8859_15 LATIN9 ISO-8859-16 ISO_8859_16 Why aren't you using LATINx for (some of) these as well? If LATIN6 to 9 are well defined in the SQL or some other standards, I would not object using them. I just don't have enough confidence. For ISO-8859-5 to 8, and 16, I don't see well defined standards. ISO-8859-16 *is* LATIN10, I just don't have the reference to prove it (I can look for it, if you want to). ISO-8859-5 to 8 aren't latin scripts. From memory, 5 is cyrillic, 6 is arabic, 7 is greek, 8 is ??? (hebrew ?)... So it would make sense to add LATIN10, still :) If you were sure ISO-8859-16 == LATIN10, I could add it. Ok, here is the modified encoding table (column1 is the standard name, 2 is our official name, and 3 is alias). If there's no objection, I will change them. ASCII SQL_ASCII UTF-8 UNICODE UTF_8 MULE-INTERNAL MULE_INTERNAL ISO-8859-1 LATIN1 ISO_8859_1 ISO-8859-2 LATIN2 ISO_8859_2 ISO-8859-3 LATIN3 ISO_8859_3 ISO-8859-4 LATIN4 ISO_8859_4 ISO-8859-5 ISO_8859_5 ISO-8859-6 ISO_8859_6 ISO-8859-7 ISO_8859_7 ISO-8859-8 ISO_8859_8 ISO-8859-9 LATIN5 ISO_8859_9 ISO-8859-10 LATIN6 ISO_8859_10 ISO-8859-13 LATIN7 ISO_8859_13 ISO-8859-14 LATIN8 ISO_8859_14 ISO-8859-15 LATIN9 ISO_8859_15 ISO-8859-16 LATIN10 ISO_8859_16 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Unicode combining characters
I have committed part of Patrice's patches with minor fixes. Uncommitted changes are related to the backend side, and the reason could be found in the previous discussions (basically this is due to the fact that current regex code does not support UTF-8 chars = 0x1). Instead pg_veryfymbstr() now rejects UTF-8 chars = 0x1. -- Tatsuo Ishii Hi, I should have sent the patch earlier, but got delayed by other stuff. Anyway, here is the patch: - most of the functionality is only activated when MULTIBYTE is defined, - check valid UTF-8 characters, client-side only yet, and only on output, you still can send invalid UTF-8 to the server (so, it's only partly compliant to Unicode 3.1, but that's better than nothing). - formats with the correct number of columns (that's why I made it in the first place after all), but only for UNICODE. However, the code allows to plug-in routines for other encodings, as Tatsuo did for the other multibyte functions. - corrects a bit the UTF-8 code from Tatsuo to allow Unicode 3.1 characters (characters with values = 0x1, which are encoded on four bytes). - doesn't depend on the locale capabilities of the glibc (useful for remote telnet). I would like somebody to check it closely, as it is my first patch to pgsql. Also, I created dummy .orig files, so that the two files I created are included, I hope that's the right way. Now, a lot of functionality is NOT included here, but I will keep that for 7.3 :) That includes all string checking on the server side (which will have to be a bit more optimised ;) ), and the input checking on the client side for UTF-8, though that should not be difficult. It's just to send the strings through mbvalidate() before sending them to the server. Strong checking on UTF-8 strings is mandatory to be compliant with Unicode 3.1+ . Do I have time to look for a patch to include iso-8859-15 for 7.2 ? The euro is coming 1. january 2002 (before 7.3 !) and over 280 millions people in Europe will need the euro sign and only iso-8859-15 and iso-8859-16 have it (and unfortunately, I don't think all Unices will switch to Unicode in the meantime) err... yes, I know that this is not every single person in Europe that uses PostgreSql, so it's not exactly 280m, but it's just a matter of time ! ;) I'll come back (on pgsql-hackers) later to ask a few questions regarding the full unicode support (normalisation, collation, regexes,...) on the server side :) Here is the patch ! Patrice. -- Patrice HÉDÉ --- patrice à islande org - -- Isn't it weird how scientists can imagine all the matter of the universe exploding out of a dot smaller than the head of a pin, but they can't come up with a more evocative name for it than The Big Bang ? -- What would _you_ call the creation of the universe ? -- The HORRENDOUS SPACE KABLOOIE ! - Calvin and Hobbes -- http://www.islande.org/ - ---(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] Package support for Postgres
On Sat, 13 Oct 2001, Tom Lane wrote: Bill Studenmund [EMAIL PROTECTED] writes: For functions and aggregates, things are a little more complicated. First off, there is a package called standard which contains all types, aggregates, operators, and functions which aren't in a specific package. This includes all of the standard Postgres routines, and anything created with CREATE FUNCTION, CREATE AGGREGATE, CREATE OPERATOR, and CREATE TYPE. Secondly, parsing is always done in terms of a specified package context. If we are parsing an equation in a routine inside of a package, then the package context is that package. If we are just typing along in psql, then the package context is standard. When you specify a function or aggregate, you have two choices. One is to specify a package, and a function in that package, like nametest.process to specify the process function in the nametest package. The other choice is to just give the function's name. The first place Postgres will look is in the package context used for parsing. If it's not there (and that context wasn't standard), then it will look in standard. Hmm. How does/will all of this interact with SQL-style schemas? Independent as I understand it. Schemas (as I understand Oracle schemas) operate at a level above the level where packages operate. The reason I'm concerned is that if we want to retain the present convention that the rowtype of a table has the same name as the table, I think we are going to have to make type names schema-local, just like table names will be. And if type names are local to schemas then so must be the functions that operate on those types, and therefore also operators (which are merely syntactic sugar for functions). This seems like it will overlap and possibly conflict with the decisions you've made for packages. It also seems possible that a package *is* a schema, if schemas are defined that way --- does a package bring anything more to the table? I don't think it conflicts. My understanding of schemas is rather simplistic and practical. As I understand it, they correspond roughly to databases in PG. So with schema support, one database can essentially reach into another one. Package support deals with the functions (and types and in this case aggregates and operators) that schema support would find in the other schemas/databases. I also wonder how the fixed, single-level namespace search path you describe interacts with the SQL rules for schema search. (I don't actually know what those rules are offhand; haven't yet read the schema parts of the spec in any detail...) Should be independent. The searching only happens when you are not in the standard package, and you give just a function name for a function. The searching would only happen in the current schems. If you give a schema name, then I'd expect PG to look in that schema, in standard, for that function. If you give both a schema and package name, then PG would look in that package in that schema. Also, both operators and functions normally go through ambiguity resolution based on the types of their inputs. How does the existence of a name search path affect this --- are candidates nearer the front of the search path preferred? Offhand I'm not sure if they should get any preference or not. There is no name spacing for operators in my implimentation as to have one strikes me as reducing the utility of having types and operators in a package. For functions (and aggregates), I tried to touch on that in the latter part of my message; that's what the example with process(changer(4)) was about. PG will try to type coerce a function in the current package before it looks in standard. So yes, candidates nearer the front are prefered. I'd like to see schemas implemented per the spec in 7.3, so we need to coordinate all this stuff. Sounds good. I don't think it will be that hard, though. :-) Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FAQ error
Bruce Momjian writes:
Bruce Momjian writes:
OK, new FAQ code is:
$sql = SELECT nextval('person_id_seq');
$newSerialID = ($conn-selectrow_array($sql))[0];
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
$res = $dbh-do($sql);
This code is still incorrect for any known programming language and it's
even less clear to a person that doesn't know the programming language
it's probably trying to imitate.
OK, what suggestions do you have?
I didn't have a problem with the original version. It conveyed clearly
(to me), read the nextval and insert it yourself.
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] php-psql lock problem. Thanks!
I'm not sure what the answer to your problem is, but I'm sure you have the
wrong approach.
For all practical purposes, client/server database programming is a
multiprocessing problem set. What you are trying to implement is a mutex. A
mutex is a mutual exclusion tool. You can't reliably do what you think you are
doing.
If one process asks if something is locked, and the answer is no, in the
interim time another process can do the same thing. You will still have a
deadlock situation because [n] processes can read something as unlocked, and
then set themselves on a course of action in which all will attempt to lock.
You may reduce the probability, but you can not eliminate it.
You will need to come up with a mutex protocol. i.e.
?php
$res = pg_exec($conn, select mylock());
if(pg_Result($res, mylock) == yes)){
(***)
pg_exec($conn, select myunlock());
}
else
// is locked do something else
?
The mylock and the myunlock have to work across the multiple PostgreSQL
processes and use SYSV semaphore or something to manage the lock.
So, what end result are you trying to have? Are you saying you want one user to
be able to lock a table for a series of transactions, while another can use it
in a readonly fashion? But it only gets read-only access if something is
already locked? What if it needs to update? Since you mention PHP, I assume
this is a web site or something. Since you mention login, I assume you are
writing some sort of session manager.
AFAIK SQL does not have the concept of a testable Mutex, you will have to write
your own. But if you are doing a session manager in PHP, email me directly, I
have a number of suggestions.
Maurizio Ortolan wrote:
Hello to everybody!
I've a little problem with LOCK-ing a
certain row in a table using PHP and
PostgreSQL on LINUX.
In a few words, I'd like to undertand
how find out if a certain row is locked,
in order to prevent a kind of deadlock.
Which is the (system) table where all
locked row or tables are 'saved' ?
Is there any flag?
//
Example 1:
User A:
BEGIN WORK;
select login from people where userid='1' for update;
[ ... ]
COMMIT WORK;
User B:
BEGIN WORK;
(***)
select login from people where userid='1' for update;
[ WAIT UNTIL 'COMMIT WORK' of user A ! :( ]
COMMIT WORK;
Solution:
I'd like to put in (***) a quick check in order to
know if the row with userid='1' is already locked or not.
In this way, if it's already locked, I'll use
select login from people where userid='1';
[ ONLY READ ]
instead of
select login from people where userid='1' for update;
[READ WRITE]
//
Example 2:
BEGIN WORK;
LOCK TABLE utenti IN SHARE ROW EXCLUSIVE MODE;
select login from people where userid='1';
COMMIT WORK;
//
Many thanks to everybody!
Ciao!
MaURIZIO
[EMAIL PROTECTED]
It's sure that
a small example in PHP will very very appreciated!! :))
PS: it's possible to setup a timeout for a locked table,
in order to exec an aoutomatic ROLLBACK ??
(for examples if the user goes away?
***
** Happy surfing on THE NET !! **
** Ciao by **
** C R I X 98 **
***
AntiSpam: rimuovere il trattino basso
dall'indirizzo per scrivermi...
(delete the underscore from the e-mail address to reply)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
http://www.mohawksoft.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] New contrib/tsearch module for 7.2
Thanks Tom, patch will be submitted. regards, Oleg On Fri, 12 Oct 2001, Tom Lane wrote: Oleg Bartunov [EMAIL PROTECTED] writes: we'd like to submit new module contrib/tsearch which contains implementation of new data type txtidx - a searchable data type (textual) with indexed access. Committed into contrib. I made an addition of a cast to unsigned char in the tolower() calls that didn't already have one. Without this, the regression test didn't pass. With it, it still didn't pass :-( ... but I believe your original expected file was incorrect because of the lack of cast. I committed an expected file containing the results I now get. Would you check this and confirm or deny that it's okay? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Package support for Postgres
Tom Lane writes: This seems like it will overlap and possibly conflict with the decisions you've made for packages. It also seems possible that a package *is* a schema, if schemas are defined that way --- does a package bring anything more to the table? I have been pondering a little about something I called package, completely independent of anything previously implemented. What I would like to get out of a package is the same thing I get out of package systems on operating systems, namely that I can remove all the things that belong to the package with one command. Typical packages on PostgreSQL could be the PgAccess admin tables or the ODBC catalog extensions. One might think that this could also be done with schemas. I'm thinking using schemas for this would be analogous to installing one package per directory. Now since we don't have to deal with command search paths or file system mount points there might be nothing wrong with that. Packages typically also have post-install/uninstall code, as does this proposed implementation, so that would have to be fit in somewhere. This is basically where my thinking has stopped... ;-) Now I'm also confused as to what this package system really represents: Is it a namespace mechanisms -- but Oracle does have schemas; or is it a package manager like I had in mind -- for that it does too many things that don't belong there; or is it a mechanism to set up global variables -- that already exists and doesn't need packages. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Pre-forking backend
How would authentication and access control be done with a preforking backend? I personally find a preforking backend desirable, but that's just me. But if people really want preforking how about not doing it in the backend. Create a small program that makes a few connections to postgresql, does some initialization, preconnects to various DBs (or maybe limited to one DB specified on startup), and listens on one port/socket. It might not even prefork, just cache connections so first connection is slow, subsequent ones are cached along with the user-pass for faster authentication. Then your apps can connect to that small program, authenticate, and get the relevant connection. Call it a Listener if you want ;). It does mean double the number of processes. But if done decently it is likely to mean two less complex and less buggy processes, compared to one more complex process. Would the performance be that much lower using this method? There are other configurations possible with this approach e.g.: app--unixsocket--listener--SSL--backend on another host. This configuration should reduce the TCP and SSL connection set up times over a network. Could have different types of preforkers. Then if a certain mode gets very popular and performance is insufficient then it could be appropriate to move that mode to the backend. Cheerio, Link. At 03:55 PM 13-10-2001 -0400, Bruce Momjian wrote: I realize this is all pie-in-the-sky but I think we need some connection pooling capability in the backend someday. We are fine with Apache and PHP becuase they can pool themselves but at some point we have too many clients reinventing the wheel rather than having our backend do it. Also, this relates to pre-forking backends and does not related to re-using backends, which is another nice feature we should have someday. Added to TODO: I haven't seen a consensus yet. True. I can remove it or improve it. It is actually: * Have pre-forked backend pre-connect to last requested database or pass file descriptor to backend pre-forked for matching database which mentions passing file descriptors to backends, which we have discussed and should be recorded for posterity. ---(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] Deadlock? idle in transaction
Also note that an uncommitted select statement will lock the table and prevent vacuum from running. It isn't just inserts/updates that will lock and cause vacuum to block, but selects as well. This got me in the past. (Of course this is all fixed in 7.2 with the new vacuum functionality that doesn't require exclusive locks on the tables). thanks, --Barry Michael Meskes wrote: On Thu, Oct 11, 2001 at 08:26:48PM -0400, Tom Lane wrote: You evidently have some client applications holding open transactions Okay, I know where to look for that. Thanks. that have locks on some tables. That's not a deadlock --- at least, It is no deadlock if the transaction holding the lock remains idle and does nothing. But I cannot imagine how this could happen. What happens if there is a real deadlock, i.e. the transaction holding the lock tries to lock a table vacuum already locked? Ah, I just checked and rendered my last mail useless. It appears the backend does correctly detect the deadlock and kill one transaction. it's not Postgres' fault. The VACUUM is waiting to get exclusive access to some table that's held by one of these clients, and the COPY is probably queued up behind the VACUUM. So the reason is that the transaction does hold a lock but does not advance any further? Michael ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] EXTRACT broken
Oh yeah. We don't have a date_part(units, time) function defined, so it is getting converted to interval (which in other contexts *does* have some usefulness as a time equivalent). You're going to have an extremely hard time convincing me of that. OK, thanks for the warning. I'll try later when I have more time... We could fairly easily define a date_part() for the time and timetz data types. I had figured that time would be cast to timestamp. Which is probably what it used to do. Tom Lane pointed out the problem of inferring an appropriate date for the upcast. - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] How do I get the current time in seconds in the unix
In 7.1 I was able to get this (I thought) with
date_part(''epoch'', timestamp ''now'') . That doesn't seem to work for me
in last week's -current.
Thomas, I think you broke something.
It was actually a side effect of changing the date/time parser to no
longer ignore unrecognized text fields. The previous behavior has been
there from the Beginning, and the new behavior meant that the search
routine no longer returns ignore as a status (which caused the calling
routine to drop into the special case tests including epoch).
Anyway, I've got patches, so no worries...
- Thomas
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
