[GENERAL] What is statement ID of table?
Hi, Could any one please tell me what is statement ID of table? How to get it and in which scenarios it can be helpful? Any documentation or example about statement ID would also really helpful for me. Thanks, Jignesh
Re: [GENERAL] What is statement ID of table?
Thanks Andreas. Sorry for confusion here. I mean statement ID that can be associated with prepared query(not table) to improve performance of building query. I just need to find plan using statement ID and execute it. I don't know how to do this. Thanks, Jignesh On Sun, Oct 11, 2009 at 8:06 PM, A. Kretschmer < andreas.kretsch...@schollglas.com> wrote: > In response to Jignesh Shah : > > Hi, > > > > Could any one please tell me what is statement ID of table? > > There isn't such ID, but every table has an OID, an Object Identifier. > > > > How to get it and > > The (hidden) column "oid" of pg_class contains this OID. > > > > in which scenarios it can be helpful? Any documentation or example about > > I think, you don't need this, it is only for internal. > > http://www.postgresql.org/docs/8.4/interactive/datatype-oid.html > > > Regards, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] Syntax error in trigger
Hi All,
I have written below trigger and applied on the table but it is giving
syntax error when it gets invoked. Could you please help me what is wrong? I
have given complete details here:
*my trigger function:*
**
CREATE OR REPLACE FUNCTION techdb_logtable_trigger()
RETURNS trigger AS
$BODY$
my $id= $_TD->{new}{'id'};
my $query = "
INSERT INTO log_table(id)
* SELECT $1
EXCEPT SELECT id --> throwing error for here: syntax error at or near
"EXCEPT"*
FROM loggingtable
WHERE id = $1;";
if (exists($_SHARED{$query})) {
$plan = $_SHARED{$query};
} else {
$plan = spi_prepare($query, 'INTEGER');
$_SHARED{$query} = $plan;
}
spi_exec_prepared($plan, '$id');
$BODY$
LANGUAGE 'plperl' VOLATILE SECURITY DEFINER
*Tables and trigger definitions:*
CREATE TABLE techdb_table( id integer NOT NULL, info varchar NOT NULL );
CREATE TABLE log_table( id integer NOT NULL, txid integer NOT NULL DEFAULT
txid_current(), txtime timestamp NOT NULL DEFAULT transaction_timestamp());
CREATE TRIGGER techdb_trigger
BEFORE INSERT ON techdb_table
FOR EACH ROW EXECUTE PROCEDURE techdb_logtable_trigger();
*Error message:*
Executing "insert into techdb_table values(1, 'test');" command gives below
error.
ERROR: error from Perl function "techdb_logtable_trigger": syntax error at
or near "EXCEPT" at line 15.
** Error **
ERROR: error from Perl function "techdb_logtable_trigger": syntax error at
or near "EXCEPT" at line 15.
SQL state: XX000
Thanks,
Jignesh
Re: [GENERAL] Syntax error in trigger
Thanks Anreas but I think that's not a issue. The issue is something to do
with "$1". I don't know how to get rid of it.
techdb=# INSERT INTO log_table(id) SELECT 5 EXCEPT SELECT id FROM log_table
WHERE id = 5;
INSERT 0 1
techdb=# select * from log_table;
id | txid | txtime
++
5 | 196552 | 2010-04-22 09:30:10.509326
(1 row)
techdb=#
Could anyone please help me out here.
Thanks,
Jignesh
On Wed, Apr 21, 2010 at 9:04 PM, Andreas Kretschmer <
akretsch...@spamfence.net> wrote:
> Jignesh Shah wrote:
>
> > Hi All,
> >
> > I have written below trigger and applied on the table but it is giving
> syntax
> > error when it gets invoked. Could you please help me what is wrong? I
> have
> > given complete details here:
> >
> > my trigger function:
> >
> > CREATE OR REPLACE FUNCTION techdb_logtable_trigger()
> > RETURNS trigger AS
> > $BODY$
> > my $id= $_TD->{new}{'id'};
> > my $query = "
> >INSERT INTO log_table(id)
> >SELECT $1
> >EXCEPT SELECT id --> throwing error for here: syntax error at or
> near
>
> Select from which table? I think, there is the table-name missing,
> right?
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] Syntax error in trigger
I got it resolved. I just need to put below statement into single quote
instead of double quote. :)
my $query ='INSERT INTO log_table(id) SELECT $1 EXCEPT SELECT id FROM
log_table WHERE id = $1;';
Thanks,
Jignesh
On Thu, Apr 22, 2010 at 2:02 PM, Jignesh Shah wrote:
> Thanks Anreas but I think that's not a issue. The issue is something to do
> with "$1". I don't know how to get rid of it.
>
> techdb=# INSERT INTO log_table(id) SELECT 5 EXCEPT SELECT id FROM log_table
> WHERE id = 5;
> INSERT 0 1
> techdb=# select * from log_table;
> id | txid | txtime
> ++
> 5 | 196552 | 2010-04-22 09:30:10.509326
> (1 row)
> techdb=#
>
> Could anyone please help me out here.
>
> Thanks,
> Jignesh
> On Wed, Apr 21, 2010 at 9:04 PM, Andreas Kretschmer <
> akretsch...@spamfence.net> wrote:
>
>> Jignesh Shah wrote:
>>
>> > Hi All,
>> >
>> > I have written below trigger and applied on the table but it is giving
>> syntax
>> > error when it gets invoked. Could you please help me what is wrong? I
>> have
>> > given complete details here:
>> >
>> > my trigger function:
>> >
>> > CREATE OR REPLACE FUNCTION techdb_logtable_trigger()
>> > RETURNS trigger AS
>> > $BODY$
>> > my $id= $_TD->{new}{'id'};
>> > my $query = "
>> >INSERT INTO log_table(id)
>> >SELECT $1
>> >EXCEPT SELECT id --> throwing error for here: syntax error at or
>> near
>>
>> Select from which table? I think, there is the table-name missing,
>> right?
>>
>>
>> Andreas
>> --
>> Really, I'm not out to destroy Microsoft. That will just be a completely
>> unintentional side effect. (Linus Torvalds)
>> "If I was god, I would recompile penguin with --enable-fly." (unknown)
>> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
[GENERAL] Issue in Improving the performance using prepared plan
Hi,
I have written following trigger and trying to improve the performance by
using prepared query everytime. I have used spi_prepare to prepare the query
and $_SHARED global hash to persist the prepared plan but it doesn't seem to
work. Though $query will be same always in following trigger, it prepares
query everytime and never uses prepared plan.
Could anyone tell me what's wrong going on?
CREATE OR REPLACE FUNCTION techdb_table_trigger()
RETURNS trigger AS
$BODY$
our ($id, $query, $plan, $change_log_table);
$change_log_table = "ChangeLogTable";
$id = $_TD->{new}{'id'};
$query = (< Always
comes here. Don't know why?*
} else {
$plan = spi_prepare($query, 'INTEGER');
$_SHARED{$query} = $plan;
* elog(INFO, "###Using already prepared the
query##"); --> Never comes here.*
}
spi_exec_prepared($plan, $id);
$BODY$
LANGUAGE 'plperl' VOLATILE SECURITY DEFINER
Thanks,
Jignesh
Re: [GENERAL] Issue in Improving the performance using prepared plan
:) I realized that. Thanks. On Thu, Apr 22, 2010 at 6:53 PM, Greg Sabino Mullane wrote: > > -BEGIN PGP SIGNED MESSAGE- > Hash: RIPEMD160 > > > > I have written following trigger and trying to improve the performance by > > using prepared query everytime. I have used spi_prepare to prepare the > query > > and $_SHARED global hash to persist the prepared plan but it doesn't seem > to > > work. Though $query will be same always in following trigger, it prepares > > query everytime and never uses prepared plan. > > Could anyone tell me what's wrong going on? > > Works fine for me. Note that your elog outputs are switched - you are > claiming > the already prepared plan for the first time (if exists) and claiming the > first prepare when in fact it is reusing (else). > > - -- > Greg Sabino Mullane g...@turnstep.com > End Point Corporation http://www.endpoint.com/ > PGP Key: 0x14964AC8 201004220922 > http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 > -BEGIN PGP SIGNATURE- > > iEYEAREDAAYFAkvQTasACgkQvJuQZxSWSsiH1wCgwiuBRmjmGZ0WWKKD/6BwovhR > M7IAoME88RAuNAd0P1tH4ug/I8FFJ8Bj > =CG70 > -END PGP SIGNATURE- > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] cannot use column references in default expression?
Could anyone please help me to get rid of following error? I want to set the 'ishuman' column based on the value of 'ID' column but it is not allowing me to do so. Any alternatives? techdb=> CREATE TABLE Users ( ID INTEGER, isHumanBOOLEAN NOT NULL DEFAULT (ID IS NULL) CHECK (isHuman = ID IS NULL), Name VARCHAR NOT NULL); ERROR: cannot use column references in default expression techdb=> Thanks, Jignesh
Re: [GENERAL] cannot use column references in default expression?
Trigger should be the last solution. This used to be working but I think with latest postgresql upgrade, this stopped working. Might be someone around here knows whats going on here. Thanks, Jignesh On Fri, May 28, 2010 at 11:00 PM, Craig Ringer wrote: > On 29/05/2010 1:20 AM, Jignesh Shah wrote: > >> Could anyone please help me to get rid of following error? I want to set >> the 'ishuman' column based on the value of 'ID' column but it is not >> allowing me to do so. Any alternatives? >> > > Use a BEFORE trigger to set it. > > -- > Craig Ringer >
[GENERAL] Setting permissions to access schema and language
Hi, I know how to set the permissions for tables. Could anyone tell me how to restrict people accessing and creating schemas? Also, is it possible to restrict language permissions? Suppose I want only few users should use C language and for rest of users it should be missing. How can I restrict permissions on languages? Thanks in advanced, Jignesh
[GENERAL] How to get the users name from users group?
Hello All, I have created role "database-users" and inserted some of users inside it. Could you tell me which query to use for listing out those users name? I went through pg_users, pg_group but no luck. CREATE ROLE database-users NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; CREATE ROLE "dbuser1" LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO "dbuser1"; CREATE ROLE "dbuser2" LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO "dbuser2"; How to query database-users to list the dbuser1 and dbuser2? Thanks in advance, Jack
Re: [GENERAL] How to get the users name from users group?
Exactly the same! Thanks a ton. On Fri, Feb 19, 2010 at 3:58 PM, Magnus Hagander wrote: > 2010/2/19 Jignesh Shah : > > Hello All, > > > > I have created role "database-users" and inserted some of users inside > it. Could you tell me which query to use for listing out those users name? I > went through pg_users, pg_group but no luck. > > > > CREATE ROLE database-users > > NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; > > > > CREATE ROLE "dbuser1" LOGIN > > NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; > > GRANT database-users TO "dbuser1"; > > > > CREATE ROLE "dbuser2" LOGIN > > NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; > > GRANT database-users TO "dbuser2"; > > > > How to query database-users to list the dbuser1 and dbuser2? > > I think you're looking for: > SELECT rolname from pg_authid a INNER JOIN pg_auth_members m ON > m.member=a.oid WHERE m.roleid=(SELECT oid FROM pg_authid WHERE > rolname='database-users') > > or something similar to that. > > > -- > Magnus Hagander > Me: http://www.hagander.net/ > Work: http://www.redpill-linpro.com/ >
[GENERAL] Questions regarding SET option.
Hello All, I have been writing a function with SECURITY DEFINER enabled. Basically, I am looking for ways to override the users SET option settings while executing my function to prevent the permissions breach. For example, to override "SET search_path", I am setting search path in my function before executing anything. Could any one please tell me what could be other SET options that I should take care? Moreover, how to revert back those settings just before returning from my function? Thanks, Jack
Re: [GENERAL] Questions regarding SET option.
Thanks a ton Laurenz and Pavel for your responses but I really didn't follow you. I am not master in PostGreSQL yet. Could you please give me some example? Basically, I want to know how many such SET options I should reset before executing my function and at the end it should also be restored to original settings. It would be really helpful if you could elaborate your response. Thanks guys. Jack On Mon, Feb 22, 2010 at 8:05 PM, Albe Laurenz wrote: > Jignesh Shah wrote: > > I have been writing a function with SECURITY DEFINER enabled. > > Basically, I am looking for ways to override the users SET > > option settings while executing my function to prevent the > > permissions breach. For example, to override "SET > > search_path", I am setting search path in my function before > > executing anything. Could any one please tell me what could > > be other SET options that I should take care? > > > > Moreover, how to revert back those settings just before > > returning from my function? > > You can use the SET clause of CREATE FUNCTION which does exactly > what you want. > > Yours, > Laurenz Albe >
Re: [GENERAL] Questions regarding SET option.
>> set work_mem to '1MB' >> set search_path = 'public'; Thanks for the example Pavel. I understood it. Are there any other SET options except above that I need to set to prevent security breach? Thanks, Jack On Mon, Feb 22, 2010 at 11:41 PM, Pavel Stehule wrote: > 2010/2/22 Jignesh Shah : > > Thanks a ton Laurenz and Pavel for your responses but I really didn't > follow > > you. I am not master in PostGreSQL yet. Could you please give me some > > example? > > > > Basically, I want to know how many such SET options I should reset before > > executing my function and at the end it should also be restored to > original > > settings. > > > > create or replace function foop() > returns int as $$ > select 10 > $$ language sql > set work_mem to '1MB' > set search_path = 'public'; > CREATE FUNCTION > postgres=# > > regards > Pavel Stehule > > > It would be really helpful if you could elaborate your response. > > > > Thanks guys. > > Jack > > > > On Mon, Feb 22, 2010 at 8:05 PM, Albe Laurenz > > wrote: > >> > >> Jignesh Shah wrote: > >> > I have been writing a function with SECURITY DEFINER enabled. > >> > Basically, I am looking for ways to override the users SET > >> > option settings while executing my function to prevent the > >> > permissions breach. For example, to override "SET > >> > search_path", I am setting search path in my function before > >> > executing anything. Could any one please tell me what could > >> > be other SET options that I should take care? > >> > > >> > Moreover, how to revert back those settings just before > >> > returning from my function? > >> > >> You can use the SET clause of CREATE FUNCTION which does exactly > >> what you want. > >> > >> Yours, > >> Laurenz Albe > > > > >
[GENERAL] Not able to change the owner of function
Hi, could you tell me what could be the issue in below command. I could see that there is an option for changing OWNER of function but not sure why it is giving this error. techdb=# ALTER FUNCTION test_create() SET OWNER TO masanip; ERROR: unrecognized configuration parameter "owner" techdb=# Thanks in advance, Jack
[GENERAL] How to get the permissions assigned to user?
Hi, Is there any way to get the set of permissions list assigned to user? I want to know whether user has create table permissions on particular schema or not? Thanks in advance, Jack
Re: [GENERAL] [NOVICE] Not able to change the owner of function
Hey, I have read it and current user is 'postgres' and the new_user is also
looks fine but still the same error.
techdb=# select current_user;
current_user
--
postgres
(1 row)
techdb=# CREATE OR REPLACE FUNCTION test_create()
RETURNS void AS
$BODY$
$cmd = "CREATE TABLE testtable(col varchar not null);";
spi_exec_query("CREATE OR REPLACE FUNCTION myfunc() RETURNS void AS '$cmd'
LANGUAGE SQL;"); spi_exec_query("SELECT myfunc();");
$BODY$
LANGUAGE 'plperl';
CREATE FUNCTION
techdb=# ALTER FUNCTION test_create() SET OWNER TO user1;
ERROR: unrecognized configuration parameter "owner"
techdb=#
Thanks,
Jack
On Wed, Feb 24, 2010 at 11:51 AM, A. Kretschmer <
andreas.kretsch...@schollglas.com> wrote:
> In response to Jignesh Shah :
> > Hi,
> >
> > could you tell me what could be the issue in below command. I could see
> that
> > there is an option for changing OWNER of function but not sure why it is
> giving
> > this error.
> >
> > techdb=# ALTER FUNCTION test_create() SET OWNER TO masanip;
> > ERROR: unrecognized configuration parameter "owner"
>
> test=*# \h alter function
> Command: ALTER FUNCTION
> Description: change the definition of a function
> Syntax:
> ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
>OWNER TO new_owner
>
> Read that and try:
>
> ALTER FUNCTION test_create() OWNER TO masanip;
>
>
> Regards, Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-novice mailing list (pgsql-nov...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
Re: [GENERAL] Not able to change the owner of function
Thanks :) I am going crazy at times. On Wed, Feb 24, 2010 at 12:04 PM, Frank Heikens wrote: > Skip the SET-keyword: > > ALTER FUNCTION test_create() OWNER TO masanip; > > Regards, > Frank > > > > Op 24 feb 2010, om 07:14 heeft Jignesh Shah het volgende geschreven: > > Hi, > > could you tell me what could be the issue in below command. I could see > that there is an option for changing OWNER of function but not sure why it > is giving this error. > > techdb=# ALTER FUNCTION test_create() SET OWNER TO masanip; > ERROR: unrecognized configuration parameter "owner" > techdb=# > > Thanks in advance, > Jack > > > > > >
[GENERAL] has_schema_privilege function
Hi, I have created below function. I am checking return value of
has_schema_privilege by using flag="f". I think this is not strong way to
make a check because if in future "f" becomes "false" my stored procedure
will work improper.
Could you tell me is there any other robust way to make sure that user1
doesn't have CREATE permissions on mydb schema?
CREATE OR REPLACE FUNCTION schema_perm_test()
RETURNS void AS
$BODY$
$rv = spi_exec_query("SELECT has_schema_privilege('user1', 'mydb',
'CREATE') AS flag;");
if(lc($rv->{rows}->[0]->{flag}) eq "f") {
# Do tasks
}
$BODY$
LANGUAGE 'plperl' VOLATILE SECURITY DEFINER
Thanks.
Re: [GENERAL] Get the list of permissions on schema for current user
I don't think you can do it. You have to parse the string you got from
pg_namespace to get the current user's permissions.
On Thu, Apr 1, 2010 at 11:09 AM, dipti shah wrote:
> Hi,
>
> I ran below command to list out all privileges of objects if "mydb" schema.
> Actually, I want to know what are the permissions "user1" has on mydb
> schema. Could you please tell me how to do this?
>
> mydb=# select pc.relname, pc.relacl from pg_class pc, pg_namespace pn where
> pc.relnamespace=pn.oid and pn.nspname='mydb';
> relname |relacl
>
> --+---
> mylog |
> {postgres=arwdDxt/postgres,=arwdDxt/postgres}
> techtable |
> {postgres=arwdDxt/postgres,=ar/postgres}
> techtable_log |
> hrtable |
> {postgres=arwdDxt/postgres,=ar/postgres}
> hrtable_log |
> (5 rows)
>
>
> mydb=> select current_user;
> current_user
> --
> user1
> (1 row)
>
> mydb=>
>
> Thanks, Dipti
>
>
