Re: [HACKERS] pg_dump and inserts
Bruce Momjian wrote: I just checked and pg_dump -d _doesn't_ place the INSERT's in a transsaction. Seems it should, and perhaps add a: SET autocommit TO 'on' as well. Of course, that SET would fail when restoring to prior releases, but they don't have autocommit off anyway so it can be ignored. Comments? This would certainly speed up loads that use INSERT. I'm not sure that pg_dump is the right place to do this, unless it's something that can be turned on/off with a command line switch (remember that editing the file to delete or comment out the transaction commands isn't necessarily feasible). It seems to me that a DBA might want to have a bit more control over this behavior. So: if pg_restore or some other utility is used to perform the restore, then that utility should issue the BEGIN/END on behalf of the user. One reason I can think of for keeping manual control over the transaction is the case where one wishes to restore from multiple dumps. In that case, it could be very useful to issue a single transaction block around the entire thing, and to examine the restored data before actually committing the results, in case something doesn't look right. This is all complicated, of course, by commands which cannot occur within transactions, which is why I think a switch controlling this behavior is appropriate. I certainly don't have a problem with the default being that the transaction commands are issued in the dump, as long as it's a behavior that can be turned off. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] (Fwd) Re: [LIP] PostgreSQL stored Procedure
Hi all, I had this with me to submit as a bug report for a misleading error message. Is this the place I send this or I need to send it to someplace else? Shridhar --- Forwarded message follows --- Date sent: Thu, 26 Dec 2002 12:39:59 +0530 From: Rajesh Fowkar [EMAIL PROTECTED] To: Shridhar Daithankar [EMAIL PROTECTED] Subject:Re: [LIP] PostgreSQL stored Procedure Organization: V. S. Dempo Co. Pvt. Ltd. http://www.dempos.com On Thu, Dec 26, 2002 at 11:51:10AM +0530, Shridhar Daithankar wrote: On 26 Dec 2002 at 10:05, Rajesh Fowkar wrote: I am on n number of postgresql lists. If you send me complete description of problem and how you fixed it, it could be forwarded for some better resolution. What happened was, in my stored procedure there was a varchar parameter passed. Sorry for the long lines. Just pasted the function below. CREATE FUNCTION grn_actions(varchar,char,char,integer,integer,char,char,date,char,char,char,char ,varchar,date,varchar,varchar) returns char as ' DECLARE vdocument_type alias for $1; Now below there is a line to compare this vdocument_type if ( (vdocument_type = ''PO'') or (vdocument_type = ''LOCAL'') ) then insert into goods_receipt_note values( cyear_id,cgrn_type,new_transaction_id,dgrn_date, cpurchase_order_year_id,cpurchase_order_type, cpurchase_order_number,csupplier_code,vbill_number,dbill_date, vchallan_number,dchallan_date,vtransport,nfreight,vremark,false); end if; In the above code paranthesis for 'if' statement are correct. However when the first parathesis was missing and there was a mismatch of parenthesis postgres gave me the following error : Dec 24 11:15:54 woody postgres[2324]: [2-1] ERROR: Unable to identify an operator '=' for types 'character varying' and 'boolean' Dec 24 11:15:54 woody postgres[2324]: [2-2] You will have to retype this query using an explicit cast Dec 24 11:15:54 woody postgres[2324]: [3] NOTICE: Error occurred while executing PL/pgSQL function grn_actions Now due to the above error my full concentration went on converting either of the two values to the same data type but no sucesss, thinking that varchar cannot be compared with a string value in Postgres which was wrong. However after putting the first opening bracket to 'if' all is good. Don't you think the above error message is misleading. The error message returning mechanism as far as PL/pgSQL is concerned is not that good as yet. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Has everyone else here seen the new Database Open Test Suite byIBM?
Hi everyone, Just came across a reference to a new Open Source database test suite by IBM that supports DB2, Oracle, Sybase, PostgreSQL, and MySQL: http://sourceforge.net/project/showfiles.php?group_id=3382release_id=115190 It's part of the Linux Test Project and only supports Linux, but it looks useful. From it's Overview page: Database Opensource Test Suite (DOTS) is a set of test cases designed for the purpose of stress testing and long run testing on database systems to measure database performance and reliability. It has two kinds of test cases - Basic Cases and Advanced Cases. The primary goal of Basic Cases is stress and long run database testing; the secondary goal is 100% JDBC API coverage. There are 8 test cases written in Java to cover JDBC API under the Basic Cases category. The goal of the Advanced Cases is modeling real-world business logic, stress and long run testing on database systems. There are 2 test cases written in Java under the Advanced Cases category. Looking at the configuration info for PostgreSQL, it has no changes from the default memory configuration settings, but that's probably because they don't know enough about PostgreSQL to be aware of the need for that. Thought it worth pointing out if case people here haven't yet come across it. :-) Regards and best wishes, Justin Clift -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SET NULL on NOT NULL field
On Mon, Jan 27, 2003 at 21:23:01 -0800, Stephan Szabo [EMAIL PROTECTED] wrote: On Tue, 28 Jan 2003, Christopher Kings-Lynne wrote: I just noticed you can do this: create table blah ( a not null references test on delete set null ) Should that be prevented? It shouldn't be too hard to test for really... Maybe, although I don't think the spec prevents it. In practice I'd guess it ends up being a more expensive way of saying no action. No. You end up not being able to delete the referenced keys. I tested this in 7.3 and you get the following message when you try it: ERROR: ExecUpdate: Fail to add null value in not null attribute col1 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] SET NULL on NOT NULL field
On Tue, 28 Jan 2003, Bruno Wolff III wrote: On Mon, Jan 27, 2003 at 21:23:01 -0800, Stephan Szabo [EMAIL PROTECTED] wrote: On Tue, 28 Jan 2003, Christopher Kings-Lynne wrote: I just noticed you can do this: create table blah ( a not null references test on delete set null ) Should that be prevented? It shouldn't be too hard to test for really... Maybe, although I don't think the spec prevents it. In practice I'd guess it ends up being a more expensive way of saying no action. No. You end up not being able to delete the referenced keys. I tested this in 7.3 and you get the following message when you try it: ERROR: ExecUpdate: Fail to add null value in not null attribute col1 Right, and NO ACTION shouldn't allow you delete the referenced keys either except that it gives you a meaningful error message as well. :) I think you may have been confusing NO ACTION and CASCADE. ---(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_dump and inserts
Bruce Momjian [EMAIL PROTECTED] writes: I just checked and pg_dump -d _doesn't_ place the INSERT's in a transsaction. Seems it should, I think this is a bad idea. If one were after speed, one would have used the COPY format in the first place. If one uses INSERTs, there may be a reason for it --- like, say, wanting each row insertion to succeed or fail independently. Put a begin/end around it, and you lose that. and perhaps add a: SET autocommit TO 'on' as well. This is probably a good idea, since pg_dump scripts effectively assume that anyway. Of course, that SET would fail when restoring to prior releases, Irrelevant; current pg_dump scripts already issue a SET that pre-7.3 servers won't recognize (search_path). A failed SET is harmless anyway, or should be. (What we really need is for someone to fix pg_restore to not abort on SQL errors...) 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])
[HACKERS] Replication talk
I just completed a replication talk for the Japanese PostgreSQL Users Group. Can folks eyeball it in the next few hours and let me know if there are any improvements to be made? ftp://candle.pha.pa.us/pub/postgresql/replication.pdf Thanks. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] docbook and postgresql
Sailesh Krishnamurthy writes: sailesh:datafix~/work/postgres/doc/src/sgml: gmake admin.html /usr/bin/perl /usr/share/sgml/docbook//bin/collateindex.pl -f -g -t 'Index' -o bookindex.sgml -N Can't open perl script /usr/share/sgml/docbook//bin/collateindex.pl: No such file or directory gmake: *** [bookindex.sgml] Error 2 sailesh:datafix~/work/postgres/doc/src/sgml: Look for collateindex.pl on your system and fix the makefile to use the right path. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] poor performance of subquery in psql
1. the following query is so slow, after 12 hours, I kill it - delete from doc where cdi in (select cdi from doc_b1); doc_b1 records = 4 doc records = 500 cdi are indexed in both table. 2. I rewrite the above task in plpgsql, it takes 10 secs to finish. why psql subquery is not smarter enough to use indexes if obviously? johnl ---(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] OLE DB PostgreSQL provider
I'm looking for people who want to join OLE DB Provider project. It is partially finished. I'm looking for people who want to help. -- Marek Mosiewicz [EMAIL PROTECTED] ***r-e-k-l-a-m-a** Chcesz oszczedzic na kosztach obslugi bankowej ? mBIZNES - konto dla firm http://epieniadze.onet.pl/mbiznes ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Fix for log_min_error_messages
Bruce Momjian writes: Due to my error, log_min_error messages went into 7.3.X with the wrong default, and the wrong listed options. This fixes the bug in CVS current and 7.3.X. Patch attached. Why does panic mean off? If you set it to panic don't you still get the panic messages? I'm sure the implementation makes sense, but the way it's documented seems confusing. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Fix for log_min_error_messages
Peter Eisentraut wrote: Bruce Momjian writes: Due to my error, log_min_error messages went into 7.3.X with the wrong default, and the wrong listed options. This fixes the bug in CVS current and 7.3.X. Patch attached. Why does panic mean off? If you set it to panic don't you still get the panic messages? I'm sure the implementation makes sense, but the way it's documented seems confusing. Yep, it is really weird. Gavin didn't want to have separate routines to validate the various flags, so all the level messages can take all the possible values, and there is no 'off' so panic seems the best. only meaningful ones. I now realize panic isn't really off, but I don't expect panic to happen too often. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Request for qualified column names
Dave Cramer says it is needed for the jdbc spec, somehow. It seems kind of odd so I don't want to make too complex an implementation. --- Peter Eisentraut wrote: Bruce Momjian writes: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. Can someone explain why this is needed at all? There is a reason why the SQL standard does not provide for this information: it's not well defined. Are you trying to make up a poor substitute for updatable views? -- Peter Eisentraut [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Request for qualified column names
Bruce Momjian writes: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. Can someone explain why this is needed at all? There is a reason why the SQL standard does not provide for this information: it's not well defined. Are you trying to make up a poor substitute for updatable views? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Request for qualified column names
Could someone point me to this standard? Is that the standard for SQL syntax? I wasn't aware there was a standard for RDBMS functionality. I always assumed the features provided by the RDBMS were up to the implementers. Reggie -Original Message- From: Peter Eisentraut [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 3:59 PM To: Bruce Momjian Cc: Tom Lane; Reggie Burnett; 'Dave Cramer'; 'PostgreSQL Hackers Mailing List' Subject: Re: [HACKERS] Request for qualified column names Bruce Momjian writes: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. Can someone explain why this is needed at all? There is a reason why the SQL standard does not provide for this information: it's not well defined. Are you trying to make up a poor substitute for updatable views? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Request for qualified column names
What is needed, at least from my perspective, is a way to determine proper meta data for a given column. Is it updatable? Is it nullable? Is it part of a primary key or index? Without either the base table name or attrelid,indrelid of the table, I can't get this info. Reggie -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 4:01 PM To: Peter Eisentraut Cc: Tom Lane; Reggie Burnett; 'Dave Cramer'; 'PostgreSQL Hackers Mailing List' Subject: Re: [HACKERS] Request for qualified column names Dave Cramer says it is needed for the jdbc spec, somehow. It seems kind of odd so I don't want to make too complex an implementation. -- - Peter Eisentraut wrote: Bruce Momjian writes: My idea on this after chat with Dave was to add a GUC option that puts the schema.table.column name as the default column label, rather than just the column name. Can someone explain why this is needed at all? There is a reason why the SQL standard does not provide for this information: it's not well defined. Are you trying to make up a poor substitute for updatable views? -- Peter Eisentraut [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] [ADMIN] Cannot connect to the database (PG 7.3)
I wrote: Michiel Lange [EMAIL PROTECTED] writes: It is, somehow, not possible to connect as a user which name is completely numeric. I muttered nonsense! to myself, but darned if you're not right: regression=# create user 12345; CREATE USER regression=# \q $ psql -U 12345 regression psql: FATAL: SET SESSION AUTHORIZATION: permission denied Will look into it. After some looking, it appears the culprit is assign_session_authorization() in commands/variable.c, which is assuming that a numeric-looking parameter string should be taken as a numeric user sysid, rather than an actual user name. The reason this was done was to avoid the need to do catalog lookups when restoring a prior setting during error recovery. That's still a valid concern, so right offhand I don't see an easy fix. Any ideas? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Request for qualified column names
Bruce Momjian [EMAIL PROTECTED] writes: Dave Cramer says it is needed for the jdbc spec, somehow. Does the JDBC spec really require the database to provide functionality that's not in the SQL spec? I kinda doubt that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Testing for int64 (was Re: [COMMITTERS] pgsql-server/ /configure /configure.in...)
[EMAIL PROTECTED] (Peter Eisentraut - PostgreSQL) writes: The code that checks for the 64-bit int type now gives more reasonable results when cross-compiling: In that case we just take the compiler's information and trust that the arithmetic works. Disabling int64 is too pessimistic. It's not so much that we can't trust the arithmetic as that we shouldn't trust that the platform's s(n)printf supports int64. This situation used to be a reality on older machines with gcc but no int64 type in the native compiler, and I suspect there are still some of them out there. I think a reasonable choice in cross-compiling situations would be to assume int64 works if we have a long long int datatype, but to force use of our own snprintf rather than trusting to luck with the platform's. (It didn't look like that's what happens right now, but I might be missing something in the autoconf spaghetti.) 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])
Re: [HACKERS] [ADMIN] Cannot connect to the database (PG 7.3)
On Tue, 28 Jan 2003, Tom Lane wrote: I wrote: Michiel Lange [EMAIL PROTECTED] writes: It is, somehow, not possible to connect as a user which name is completely numeric. I muttered nonsense! to myself, but darned if you're not right: regression=# create user 12345; CREATE USER regression=# \q $ psql -U 12345 regression psql: FATAL: SET SESSION AUTHORIZATION: permission denied Will look into it. After some looking, it appears the culprit is assign_session_authorization() in commands/variable.c, which is assuming that a numeric-looking parameter string should be taken as a numeric user sysid, rather than an actual user name. The reason this was done was to avoid the need to do catalog lookups when restoring a prior setting during error recovery. That's still a valid concern, so right offhand I don't see an easy fix. Any ideas? How about throwing an error if an all digit user name is given to create user as already alluded to? Seems that would be simple, not that I know anything about the parser, but does that break any standards? -- Nigel J. Andrews ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Request for qualified column names
The method in question is ResultSetMetaDate.getTableName(int column) and while were at it ResultSetMetaData.getSchemaName(int column) and FWIW, the return value if not applicable is Dave On Tue, 2003-01-28 at 17:21, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Dave Cramer says it is needed for the jdbc spec, somehow. Does the JDBC spec really require the database to provide functionality that's not in the SQL spec? I kinda doubt that. regards, tom lane -- Dave Cramer [EMAIL PROTECTED] Cramer Consulting ---(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] Fix for log_min_error_messages
On Tue, 28 Jan 2003, Bruce Momjian wrote: Peter Eisentraut wrote: Bruce Momjian writes: Due to my error, log_min_error messages went into 7.3.X with the wrong default, and the wrong listed options. This fixes the bug in CVS current and 7.3.X. Patch attached. Why does panic mean off? If you set it to panic don't you still get the panic messages? I'm sure the implementation makes sense, but the way it's documented seems confusing. Yep, it is really weird. Gavin didn't want to have separate routines to validate the various flags, so all the level messages can take all the possible values, and there is no 'off' so panic seems the best. only meaningful ones. I offered, from memory, three different possible solutions. setting it to PANIC seemed the most popular. Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] IPv6 Support for INET/CIDR types. (fwd)
Here is Vixie's original comments on integrating Bind for IPv6. We
still need to make those customizations of display that we agreed to for
IPv4.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---BeginMessage---
I would like to see some activity on this item soon. Historically, the
CIDR stuff has arrived during beta when we can't apply any major changes.
to reiterate... i am able to fix up the postgres inet and cidr types
to make them ipv6-capable, if someone who knows the current system can do
two things:
1. import the current bind versions of inet_{net,}_{pton,ntop}.c and
move the pgsql-specific logic out of the current modified versions of
those files and up into some other part of postgres. this will keep
the current type behaviour but remove the modified bind versions of
some critical library functions.
2. change the build system to only use the postgres versions of these
functions if the system libraries (libc and libbind, mostly) don't have
them. this will ensure that bugfixes and enhancements to the BIND
versions of these files are not obscured by postgres's own versions.
if those two things were done (presumably #1 by the person who integrated
my original CIDR type and #2 by someone who knows the postgres build
system) then i would immediately do #3:
3. change the INET and CIDR type support to include IPv6.
re:
---
Vadim Kogan wrote:
Checking application/pgp-signature: FAILURE
-- Start of PGP signed section.
On Fri, Feb 22, 2002 at 02:59:34PM -0500, Bruce Momjian wrote:
Paul Vixie wrote:
I don't believe the original patch ever made it into anything.
vadim's original patch was the basis for BIND's ipv6 support in the
set of functions that pgsql borrows from BIND. so, it did make it
into _something_ just not the modified pgsql mirrors of these functions.
my proposal is that we do away with the modified pgsql mirrors of these
functions and teach the INET and CIDR handlers to wrap the BIND functions
in a way that modifies the presentation format to be postgres-compatible.
that way postgres can import new versions of, or use LIBC versions of,
the BIND functions without having to worry about local modifications to them.
Are we sure the Bind library versions are going to be on all the OS's we
support?
Eventually they will. I think that for now it's reasonable to
a) make sure that the files in postgres are _exact_ copies of those in BIND.
b) check to see whether the host provides the functionality, and if it does,
use whatever host gives us, instead of local version
c) At some point remove the local version alltogether.
Vadim.
-- End of PGP section, PGP failed!
--
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 Message---
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Windows Build System
Curtis Faith wrote: Hannu Krosing asked: Does anyone know how MySQL and interbase/firebird do it ? From the MySQL web site for version 4.0: The Windows binaries use the Cygwin library. Source code for the version of Cygwin we have used is available on this page. I think this offers a very big opportunity to differentiate. If we had project support it would make PostgreSQL a more natural choice for Windows developers. MySQL uses Cygwin for libreadline support in the client 'mysqlc'. That's pretty much it. The rest is a standard Visual C++ project that has the files moved from the unix sources via a script. -Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] New buildin function
On 23 Jan 2003, Rod Taylor wrote:
RTOn Wed, 2003-01-22 at 08:09, Olleg Samoylov wrote:
RT Hi!
RT
RT What about adding new function:
RT pg_uid()
RT pg_session_uid()
RT
RT as reference to internal function GetUserId() and GetSessionUserId().
RT
RT These can help useful, for instance in row based securety.
RT
RTDo CURRENT_USER and SESSION_USER not give those values?
Nope. CURRENT_USER and SESSION_USER return username. Sometimes need uid,
it's key usesysid in table pg_shadow, for instance, for row based
permissions. Explain in example:
create table role {
role smallinteger, -- analog group of users
name text
}
create table permission { -- link role with pg_user
uid integer references pg_user(usesysid),
role smallint references role
}
create table protected_table {
-- payload fields
access smallint references role,
author_of_last_changes integer references pg_user(usesysid) default
PG_SESSION_UID(), -- proposed function
time_of_last_changes timestamp not null default current_timestamp
}
create function update_trigger_function() returns opaque as '
begin-- PG_UID() proposed function
if (select role from role where uid=PG_UID())=old.access then
new.time_of_last_changes=current_timestamp;
new.author_of_last_changes=PG_SESSION_UID(); -- proposed function
return new;
else
return null;
end if;
end;
' language 'plpgsql';
create trigger update_trigger before update on protected table for each row
execute procedure update_trigger_function();
óertainly, I can create such function in my own project as:
create function pg_uid() returns integer as '
select usesysid from pg_user where usename=current_user;
' language 'sql';
Or as C function:
long pg_uid()
{
return GetUserId();
}
But, IMHO, such fuction must be common.
--
Olleg Samoylov
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] ECPG, threading and pooling
On Mon, Jan 27, 2003 at 10:26:53AM -0500, Tom Lane wrote: The preproc code doesn't need to be thread-safe does it? You're right of cause. Michael -- Michael Meskes Email: [EMAIL PROTECTED] ICQ: 179140304 Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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] Strange Prepare bug
I've trimmed this query down as much as possible (started out much larger), it no longer causes the error. PREPARE pg_psql_dd2(text,text) AS SELECT true FROM ( SELECT true FROM pg_catalog.pg_proc p, pg_catalog.pg_name_pattern( $2 ) AS (schpat text, propat text) WHERE p.prorettype 'pg_catalog.cstring'::pg_catalog.regtype ) AS tt, (SELECT $1 AS cmd) AS cmd; ERROR: Parameter '$1' is out of range -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
[HACKERS] Specifying Rowtypes
So currently the only way to specify a row type is by using a table, right? E.g.: CREATE TABLE t2_retval ( value1 int NOT NULL DEFAULT -1, value2 int NOT NULL, value3 int ); Are there plans to add another way of declaring this sort of thing so that I don't have to add a new table to my schema for every function that returns a rowtype? Second, it seems that there's still a problem with NULLs here: CREATE FUNCTION t2() RETURNS t2_retval AS ' DECLARE retval t2_retval%ROWTYPE; BEGIN SELECT INTO retval null, null, null; RETURN retval; END ' LANGUAGE 'plpgsql'; This is returning a row that (to my mind) doesn't match the type of the table above, because it's returning null for non-nullable columns: cjs= select coalesce(value1, -999), coalesce(value2, -999), cjs- coalesce(value3, -999) from t2(); case | case | case --+--+-- -999 | -999 | -999 (1 row) (You get the same result if you delete the SELECT INTO line above.) Am I misunderstanding something here, or is this a bug? cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Request for qualified column names
On Mon, 2003-01-27 at 10:44, Reggie Burnett wrote: Well, certainly the driver could parse the sql and extract what it thinks is the table name. It just seems quite foreign to me to have a database engine go through the motions of determining column location and have ready access to all the metadata for all the columns in a resultset and then intentionally leave all that out of the FE/BE. I think the issue is that no one has yet proposed a consistent set of behaviour for this feature, particularly in the cases that Tom raised. If you would like this feature, I'd suggest that you outline some behaviour that everyone can agree upon. Griping about intentionally left out features when the feature itself is not even well defined doesn't strike me as very productive. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] || PGP Key ID: DB3C29FC ---(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
