Integrity Error

Re: Integrity Error

On Nov 1, 2010, at 12:38 PM, Nima Ghorbany wrote:

> 
> hello guys,
> i am using openerp which uses postgres as its database .when i do some database changes in openerp i get this
error which is a reflection of postgres internall error.do u have any idea what does it mean?
> Error: "document_directory_group_rel" violates foreign key constraint "document_directory_group_rel_item_id_fkey"
> DETAIL: Key (item_id)=(230) is not present in table "document_directory".
> 
> i know that foreign key contraint avoid entering invalid data into a table but how can i find this key and
delete it?
> 
> thanks

I think error message itself is giving the information of item_id=230

Since parent table "document_director" does not have this value therefore it's not allowing to insert the
values in table document_directory_group_rel.

Thanks & Regards,
Vibhor Kumar (PCP & OCP)
ITIL V3 Cerftified.

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

plpgsql: some 'DELETE' queries are returning ROWCOUNT = 0

Hello,

I try to create some functions that will execute some queries stored
inside tables.
These 2 functions are:

[code]

CREATE FUNCTION __syncpg_execute1(sql text) RETURNS text AS $$
DECLARE
        nraffected      integer;
        retcode         text;
BEGIN
        retcode := '';
        EXECUTE sql;
        GET DIAGNOSTICS nraffected = ROW_COUNT;
        IF nraffected = 0 THEN
                retcode = 'rows_affected = 0';
        END IF;

        RETURN retcode;
EXCEPTION
        WHEN OTHERS THEN
                RETURN SQLSTATE;
END;
$$ LANGUAGE plpgsql;

[/code]

and

[code]

CREATE FUNCTION __syncpg_execute() RETURNS integer AS $$
DECLARE
        sqlrec text;
        nrrows integer;
        sqlins text;
        errstr text;
BEGIN
        nrrows := 0;

        FOR sqlrec IN SELECT sql FROM __syncpg_toexecute LOOP
                SELECT __syncpg_execute1(sqlrec) INTO errstr;
                nrrows := nrrows + 1;
                IF errstr <> '' THEN
                        sqlins = 'INSERT INTO __syncpg_toexecuteres VALUES (' ||
                                quote_literal(sqlrec) || ', ' ||
                                quote_literal(errstr) || ');';
                        EXECUTE sqlins;
                END IF;
        END LOOP;

        TRUNCATE __syncpg_toexecute;

        RETURN nrrows;
END;
$$ LANGUAGE plpgsql;

[/code]

The problem I have is that some DELETE FROM ... does not affect rows
even if those rows are there. I get the same issue with UPDATE ....

I haven't found issues with INSERT (there are lots of errors=23505 -
which is unique violation)

If I run that SQL by hand it runs successfully deleting/updating the
necessary rows.

Am I missing something?

Thank you

-- 
Best regards,
Dragos Moinescu

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Pagesize for large-objects (ONLY) database.

ssl client cert authentication

Someone asked about ssl client cert auth recently.  I got
this to work, but something tripped me up.

http://developer.postgresql.org/pgdocs/postgres/ssl-tcp.html

states (very clearly, btw) that, "To require the client to supply a
trusted certificate, place certificates of the certificate authorities
(CAs) you trust in the file root.crt in the data directory."  I had
ASS-U-MEd that root.crt would go in .postgresql as it does for encryption.

This begs the question, why two copies of the same file?

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: ssl client cert authentication

Ray Stell <stellr <at> cns.vt.edu> writes:
> Someone asked about ssl client cert auth recently.  I got
> this to work, but something tripped me up.

> http://developer.postgresql.org/pgdocs/postgres/ssl-tcp.html

> states (very clearly, btw) that, "To require the client to supply a
> trusted certificate, place certificates of the certificate authorities
> (CAs) you trust in the file root.crt in the data directory."  I had
> ASS-U-MEd that root.crt would go in .postgresql as it does for encryption.

> This begs the question, why two copies of the same file?

The one in ~/.postgresql is for client usage.  The one in $PGDATA is for
the server's use.  There's no reason to assume they'd be the same.

			regards, tom lane

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: ssl client cert authentication

On Mon, Nov 01, 2010 at 12:46:33PM -0400, Tom Lane wrote:
> Ray Stell <stellr <at> cns.vt.edu> writes:
> > Someone asked about ssl client cert auth recently.  I got
> > this to work, but something tripped me up.
> 
> > http://developer.postgresql.org/pgdocs/postgres/ssl-tcp.html
> 
> > states (very clearly, btw) that, "To require the client to supply a
> > trusted certificate, place certificates of the certificate authorities
> > (CAs) you trust in the file root.crt in the data directory."  I had
> > ASS-U-MEd that root.crt would go in .postgresql as it does for encryption.
> 
> > This begs the question, why two copies of the same file?
> 
> The one in ~/.postgresql is for client usage.  The one in $PGDATA is for
> the server's use.  There's no reason to assume they'd be the same.
> 
> 			regards, tom lane

I think I see where I went off:
 31.17. SSL Support
Changing this to: 
 31.17. Client SSL Support
would be helpful.  Also, 
 31.17.4. SSL File Usage
might be:
 31.17.4. SSL Client File Usage
They did this in the server section, so I'm not completely nuts:
 17.8.2. SSL Server File Usage

In hindsight it is very clear.  Chapter 17 is on the server and 31 is on the
client.  Adding those section title words would have helped me stay on 
course. 

Another way of providing clue would be to add $PGDATA somewhere in Table
17-3. SSL Server File Usage.  They did that sort of thing on the client side
in Table 31-4. Libpq/Client SSL File Usage.

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

reinstall postgreSQL same version while server up and running possible under linux ?

Sorry for posting this again, but my last post regarding this
problematic wasn't answered satisfyingly. (maybe I didn't find the
right words for my question  )

is this (or a minor upgrade) a "safe" way to go under linux ? I'm on a
production system and I don't want to restart the DB server while the
factory is in production, but be sure that i'm prepared the next time
I'll do a restart. Of course I could wait for a factory shutdown on a
weekend, stop server, upgrade/reinstall and restart, but I'd prefer a
"on the fly upgrade".
(this is required due to a changed upstream library dependancy (major
perl upgrade) running gentoo)

regards
Gerhard

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: reinstall postgreSQL same version while server up and running possible under linux ?

Re-sending to list since I forgot to include it on reply.

On 2010-11-02 13.59, Gerhard Hintermayer wrote:
 > Sorry for posting this again, but my last post regarding this
 > problematic wasn't answered satisfyingly. (maybe I didn't find the
 > right words for my question  )
 >
 >
 > is this (or a minor upgrade) a "safe" way to go under linux ? I'm on a
 > production system and I don't want to restart the DB server while the
 > factory is in production, but be sure that i'm prepared the next time
 > I'll do a restart. Of course I could wait for a factory shutdown on a
 > weekend, stop server, upgrade/reinstall and restart, but I'd prefer a

You can install multiple versions of PostgreSQL on Gentoo. Portage calls 
this SLOT[1].

 > "on the fly upgrade".

Depending on your definition here. You will need to restart your server 
at some point.

The options you have is dump/restore[2] or pg_upgrade[3].

 > (this is required due to a changed upstream library dependancy (major
 > perl upgrade) running gentoo)

Not sure what you are saying here.

-- 
Regards,
Robert "roppert" Gravsjö

1. http://www.gentoo.org/doc/en/handbook/handbook-x86.xml?part=2&chap=1
2. http://www.postgresql.org/docs/9.0/interactive/migration.html
3. http://www.postgresql.org/docs/9.0/interactive/pgupgrade.html

--

-- 
Sent via pgsql-admin mailing list (pgsql-admin <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: reinstall postgreSQL same version while server up and running possible under linux ?