[GENERAL] Pg.pm for Windows?
How can I use Pg.pm in my perl scripts running on Windows through ActivePerl? Some of my developers are refusing to work on Linux machines for just writing perl scripts :-). Thanks, Anand.
Re: [GENERAL] Re: where is the "applied"?
Bruce,
I would have given you a patch for refint.c (apart from the enhancements it also
solves a problem with cascading delete), but I have only 6.3.2 to create a patch
against and also because I haven't tested it on v6.5. I am again attaching the
refint.c source which I am currently using which can be used to create a patch, if
it work with the new version.
-- Anand
Bruce Momjian wrote:
> No idea why it isn't in there. Let me know what I should apply.
>
> > Hi Kai,
> >
> > It beats me too. I was under the impression that the patch was applied for
> > all versions greater than 6.3.2. I haven't bothered to upgrade since then so
> > I don't know if it was ever actually applied. Here is a working copy of
> > refint.c which I am using now, you can try using it but I haven't tested it
> > for V6.5.
> >
> > -- Anand.
> >
> > kai qu wrote:
> >
> > > hi, Anand,
> > >
> > > why I can not find it in the source code in V6.5? isn't it applied? Am I
> > > missing something ?
> > >
> > > Kai
> > >
> > > ##
> > > From: Bruce Momjian <[EMAIL PROTECTED]>
> > > To: [EMAIL PROTECTED] (Anand Surelia)
> > > Subject: Re: [GENERAL] Foreign Keys: check_primary_function
> > > Date: Sun, 14 Mar 1999 19:34:27 -0500 (EST)
> > >
> > > Applied.
> > >
> > > > Hi All,
> > > > I've changed the check_primary_key() function code to allow for either
> > >
> > > > the "automatic insert key rule" or "dependent insert key rule".
> > > > Previously it restricted the addtion of a child entry if the
> > > > corresponding parent entry was not there. Now if the option is
> > > > "automatic" it will add an entry in the parent too ( it will be
> > > > successful if there are no no-null fields in the parent apart from the
> > >
> > > > primary key).
> > > > The way to use it now is:
> > > > /*
> > > > * check_primary_key () -- check that key in tuple being
> > > > inserted/updated
> > > > * references existing tuple in "primary" table.
> > >
> > > > * Though it's called without args You have to specify referenced
> > > > * table/keys while creating trigger: key field names in triggered
> > > > table,
> > > > * referenced table name, referenced key field names,type of action
> > > > [automatic|dependent]:
> > > > * EXECUTE PROCEDURE
> > > > * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2',
> > > > '[automatic|dependent]').
> > > > */
> > > > I am attaching the new ../contrib/spi/refint.c file which will do
> > > this.
> > > > I will be glad to help in case of any problems.
> > > >
> > > > - Anand.
> > > >
> > > >
> >
> >
>
> [application/x-unknown-content-t is not supported, skipping...]
>
> --
> Bruce Momjian| http://www.op.net/~candle
> [EMAIL PROTECTED]| (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
refint.c
[GENERAL] Re: where is the "applied"?
Hi Kai,
It beats me too. I was under the impression that the patch was applied for
all versions greater than 6.3.2. I haven't bothered to upgrade since then so
I don't know if it was ever actually applied. Here is a working copy of
refint.c which I am using now, you can try using it but I haven't tested it
for V6.5.
-- Anand.
kai qu wrote:
> hi, Anand,
>
> why I can not find it in the source code in V6.5? isn't it applied? Am I
> missing something ?
>
> Kai
>
> ##
> From: Bruce Momjian <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED] (Anand Surelia)
> Subject: Re: [GENERAL] Foreign Keys: check_primary_function
> Date: Sun, 14 Mar 1999 19:34:27 -0500 (EST)
>
> Applied.
>
> > Hi All,
> > I've changed the check_primary_key() function code to allow for either
>
> > the "automatic insert key rule" or "dependent insert key rule".
> > Previously it restricted the addtion of a child entry if the
> > corresponding parent entry was not there. Now if the option is
> > "automatic" it will add an entry in the parent too ( it will be
> > successful if there are no no-null fields in the parent apart from the
>
> > primary key).
> > The way to use it now is:
> > /*
> > * check_primary_key () -- check that key in tuple being
> > inserted/updated
> > * references existing tuple in "primary" table.
>
> > * Though it's called without args You have to specify referenced
> > * table/keys while creating trigger: key field names in triggered
> > table,
> > * referenced table name, referenced key field names,type of action
> > [automatic|dependent]:
> > * EXECUTE PROCEDURE
> > * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2',
> > '[automatic|dependent]').
> > */
> > I am attaching the new ../contrib/spi/refint.c file which will do
> this.
> > I will be glad to help in case of any problems.
> >
> > - Anand.
> >
> >
refint.c
[GENERAL] How to increase shared memory buffers?
Hi All, I have been trying to increase the shared memory buffers used by Postgres by using the -B option to postmaster, but postmaster fails to start up for any value greater than 64 (which is the default in any case) saying "couldn't get shared memory - permission denied". So, (I know this should be asked on a Linux group, but...), does anybody know how can I get my Linux system to allow Postgres more shared memory? Thanks, Anand.
[GENERAL] Postgres on Sun Solaris 2.5.1
We are trying to upgrade our present Postgres 6.3.2 database running on Linux/i386 to v6.4.2 on Sun Solaris 2.5.1. I tried to compile the server using GCC for Solris 2.5.1. Everything went well except while running the regression tests, which failed giving these messages: + ERROR: Load of file /bytek/postgres/postgresql-6.4.2/src/test/regress/input/../regress.so failed: ld.so.1: bytek/postgres/bin/postmaster: fatal: relocation error: symbol not found: SPI_tuptable: referenced in /bytek/postgres/postgresql-6.4.2/src/test/regress/input/../regress.so + ERROR: Load of file /bytek/postgres/postgresql-6.4.2/src/test/regress/input/../../../../contrib/spi/refint.so failed: ld.so.1:/bytek/postgres/bin/postmaster: fatal: relocation error: symbol not found: SPI_result: referenced in /bytek/postgres/postgresql-6.4.2/src/test/regress/input/../../../../contrib/spi/refint.so I had set up the PGLIB and even the LD_LIBRARY_PATH to the postgres lib. Moreover some of the functions I had written to implement triggers using SPI failed to compile in GCC on Solaris (ld fails with fatal signal 6, while linking the shared object file) although they had compiled on GCC on Linux. My question is which compiler has been used to test the port of Postgres on Solaris 2.x. Or to reword it : Can I use GCC to compile Postgres on Solaris? Sorry for being verbose, Thanks, Anand.
[GENERAL] SPI
I'm installing v6.4.2 on Solaris 2.5.1. Everything goes well until I run the regression test. Some of the test fails with an error message like: + ERROR: Load of file /bytek/postgres/postgresql-6.4.2/src/test/regress/input/. ./regress.so failed: ld.so.1: /bytek/postgres/bin/postmaster: fatal: relocation error: symbol not found: SPI_tuptable: referenced in /bytek/postgres/postgresql- 6.4.2/src/test/regress/input/../regress.so + ERROR: Load of file /bytek/postgres/postgresql-6.4.2/src/test/regress/input/. ./../../../contrib/spi/refint.so failed: ld.so.1: /bytek/postgres/bin/postmaster : fatal: relocation error: symbol not found: SPI_result: referenced in /bytek/po stgres/postgresql-6.4.2/src/test/regress/input/../../../../contrib/spi/refint.so Has the SPI interface been changed? Thanks for your help in advance, - Anand Surelia
[GENERAL] Regression test failing on Sparc Solaris 2.5.1
I'm resending this query. It didn't evoke any response the first time, mabe it was too long :-). This time I will be more brief. I've successfully installed v6.4.2 on my Sparc Solaris 2.5.1, however it's failing a lot of regression tests. I know its normal to fail some of the tests and indeed some were really trivial. I compared the regression.diffs file to the regression.Solaris_sparc file which came with the distribution. Some of the tests that failed were not supposed to fail, like: 1. create_function_2, trigger, misc, plpgsql. Basically all failed giving error messages that a particular shared object file couldn't be loaded because of unsatisfied references. The exact messages were: Load of file /bytek/postgres/postgresql-6.4.2/src/test/regress/input/. ./regress.so failed: ld.so.1: /bytek/postgres/bin/postmaster: fatal: relocation error: symbol not found: SPI_tuptable: referenced in /bytek/postgres/postgresql-6.4.2/src/test/regress/input/../regress.so Load of file /bytek/postgres/postgresql-6.4.2/src/test/regress/input/. ./../../../contrib/spi/refint.so failed: ld.so.1: /bytek/postgres/bin/postmaster : fatal: relocation error: symbol not found: SPI_result: referenced in /bytek/postgres/postgresql-6.4.2/src/test/regress/input/../../../../contrib/spi/refint.so Load of file /bytek/postgres/postgresql-6.4.2/src/test/regress/input/. ./../../../contrib/spi/autoinc.so failed: ld.so.1: /bytek/postgres/bin/postmaster: fatal: relocation error: symbol not found: SPI_result: referenced in /bytek/postgres/postgresql-6.4.2/src/test/regress/input/../../../../contrib/spi/autoinc.so Load of file /bytek/postgres/lib/plpgsql.so failed: ld.so.1: /bytek/postgres/bin/postmaster: fatal: relocation error: symbol not found: SPI_tuptable: referenced in /bytek/postgres/lib/plpgsql.so Did I miss something? I had set the PGLIB varialble and gone ahead and set the LD_LIBRARY_PATH to the same value also. No luck. Thanks, Anand Surelia.
[GENERAL] Deadlock on Linux
Hi, I am running Postgres 6.3.2 on Caldera Linux 2.0.35. There are a few scripts written in Perl using Pg.pm which tune up the database nightly. They've been running for more than three months. However, since last month one of the scripts is failing to execute, giving the following message NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause. There is no entry for lock in section 1 of the man on my system. There is one entry however for lock which says that this call is not implemented in the Linux kernel 1.2.4 and it returns a -1 always. Does anyone have any idea what is happening? The script in question runs fine when I run it independent of the cron. Thanks for your help in advance, Anand Surelia.
[GENERAL] Timestamp
Hi , Can anyone tell me how to add a constant time interval ( say 30 seconds ) to a timestamp field and convert the result back to a valid timestamp? Thanks, Anand
[GENERAL] SPI and Libpq
Hi, Can anyone tell me what is the difference between the SPI and the Libpq interfaces and when to use what? I am writing some functions that will be used by triggers. What interface should I use to implement those functions? Thanks, Anand
[GENERAL] Foreign Keys: check_primary_function
Hi All,
I've changed the check_primary_key() function code to allow for either
the "automatic insert key rule" or "dependent insert key rule".
Previously it restricted the addtion of a child entry if the
corresponding parent entry was not there. Now if the option is
"automatic" it will add an entry in the parent too ( it will be
successful if there are no no-null fields in the parent apart from the
primary key).
The way to use it now is:
/*
* check_primary_key () -- check that key in tuple being
inserted/updated
* references existing tuple in "primary" table.
* Though it's called without args You have to specify referenced
* table/keys while creating trigger: key field names in triggered
table,
* referenced table name, referenced key field names,type of action
[automatic|dependent]:
* EXECUTE PROCEDURE
* check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2',
'[automatic|dependent]').
*/
I am attaching the new ../contrib/spi/refint.c file which will do this.
I will be glad to help in case of any problems.
- Anand.
/*
* refint.c -- set of functions to define referential integrity
* constraints using general triggers.
*/
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* -"- and triggers */
#include /* tolower () */
HeapTuple check_primary_key(void);
HeapTuple check_foreign_key(void);
typedef struct
{
char *ident;
int nplans;
void **splan;
} EPlan;
static EPlan *FPlans = NULL;
static int nFPlans = 0;
static EPlan *PPlans = NULL;
static int nPPlans = 0;
static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans);
/*
* check_primary_key () -- check that key in tuple being inserted/updated
* references existing tuple in "primary" table.
* Though it's called without args You have to specify referenced
* table/keys while creating trigger: key field names in triggered table,
* referenced table name, referenced key field names,type of action
[automatic|dependent]:
* EXECUTE PROCEDURE
* check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1',
'Pkey2','[automatic|dependent]').
*/
HeapTuple /* have to return HeapTuple to
Executor */
check_primary_key()
{
Trigger*trigger;/* to get trigger name */
int nargs; /* # of args specified in
CREATE TRIGGER */
char **args; /* arguments: column names and table
name */
int nkeys; /* # of key columns (=
(nargs-1) / 2) */
Datum *kvals; /* key values */
char *relname;/* referenced relation name */
char *action; /* action on insert or update*/
Relationrel;/* triggered relation */
HeapTuple tuple = NULL; /* tuple to return */
TupleDesc tupdesc;/* tuple description */
EPlan *plan; /* prepared plan */
Oid*argtypes = NULL;/* key types to prepare execution plan */
boolisnull; /* to know is some column NULL or not
*/
charident[2 * NAMEDATALEN]; /* to identify myself */
int ret;
int i;
/*
* Some checks first...
*/
/* Called by trigger manager ? */
if (!CurrentTriggerData)
elog(ERROR, "check_primary_key: triggers are not initialized");
/* Should be called for ROW trigger */
if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event))
elog(ERROR, "check_primary_key: can't process STATEMENT events");
/* If INSERTion then must check Tuple to being inserted */
if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
tuple = CurrentTriggerData->tg_trigtuple;
/* Not should be called for DELETE */
else if (TRIGGER_FIRED_BY_DELETE(CurrentTriggerData->tg_event))
elog(ERROR, "check_primary_key: can't process DELETE events");
/* If UPDATion the must check new Tuple, not old one */
else
tuple = CurrentTriggerData->tg_newtuple;
trigger = CurrentTriggerData->tg_trigger;
nargs = trigger->tgnargs;
args = trigger->tgargs;
if ((nargs-1) % 2 != 1) /* odd number of arguments! */
elog(ERROR, "check_primary_key: even number of arguments should be
specified");
nkeys = (nargs-1) / 2;
action=args[nargs -1];
if (strcmp(action,"automatic") && strcmp(action,"dependent"))
elog(ERROR,"check_p
[GENERAL] Re:Foreign Keys
Hi, I've managed to hack the code for check_primary_key. It now allows for a option [automatic|dependent]. It'll automatically insert into the parent table if the primary key doesn't exists in the parent table and if the option is "automatic". It works the same as before if the option is "dependent". If anybody is interested I might polish the code a bit and post to the list. Thanks, Anand.
[GENERAL] Foreign Key
Hi, I am using the triggers provided under contrib/spi/refint.* to implement referential integrity in my database. The check_primary_key function doesn't allow me to enter in a child table if a corresponding primary key doesn't exists in the parent table ( implements 'dependency'). However I also want to 'automatically' add a corresponding entry in the parent table in some of the relationships. There is no option in the check_primary _key function for that. Is there a newer version of the function available which will allow me to specify what 'insert key rule' I want to implement? If there is none then I may have to hack the code of check_primary_key to do it. Is there someone who can help me do it faster? Thanks, Anand
[GENERAL] Check constraint
Hi, I have created a function "int fname(void)" in C. When I create this function in Postgres as returning int4 and then do a " create table dummy (level int4 check(fname() > 4));" the creation fails saying that : ERROR: There is no operator '>' for types 'bool' and 'int4' You will either have to retype this query using an explicit cast, or you will have to define the operator using CREATE OPERATOR I've tried this for different operators and it comes up with the same error even though I know the operators exist. However, when I create fname as returning int2 and then create the table, it says: NOTICE: PortalHeapMemoryFree: 0x8178389 not in alloc set! CREATE Which means that the table has been created. Now when I do a insert into the table it fails saying: PQexec() -- Request was sent to backend, but backend closed the channel before r esponding. This probably means the backend terminated abnormally before or while pr ocessing the request. Could someone please explain what is going on? Do the Check constraints do not take functions? Thanks, Anand
[GENERAL] Database Connect Permission
Hi, Is there any way to specify connect permissions to a database for specific users? Right now, any user can connect to any database even though he may not be able to see any tables. Thanks, Anand Surelia.
