[GENERAL] How to discard partially retrieved result set with the C API?

[邓尧]

When single row mode is enabled, after retrieving part of the result set,
I'm no longer interested in the rest of it (due to error handling or other
reasons). How can I discard the result set without repeatedly calling
PQgetResult() in such situation ?
The result set may be quite large and it's inefficient to call
PQgetResult() repeatedly, so it's necessary to do so sometimes.

Thanks
Yao

Re: [GENERAL] Weird "function does not exist" error.

[邓尧]

My apologies, I connected to the wrong database in my C code ...

Sorry, if this E-mail bothers you.


On Mon, Nov 25, 2013 at 5:33 PM, 邓尧  wrote:

> Hi,
>
> I ran into a "function  does not exist" error when invoking a function
> created by myself. What's weird is that the function works perfectly if I
> invoke it through the command line tool "psql" but fails if I invoke it
> through the C API.
>
> The SQL to create the function is:
> CREATE OR REPLACE FUNCTION "lo_select_page"(uid_ BIGINT, lottery_type_ INT,
> type_ INT, time_start_ BIGINT, time_end_ BIGINT,
> page_number_ BIGINT, prize_status_ INT)
> RETURNS SETOF REFCURSOR AS $$
> ...
> END $$ LANGUAGE plpgsql;
>
> The SQL to invoke the function:
> BEGIN;SELECT lo_select_page(4163481095, 0, 0, 0, 189347760, 0,
> 0);FETCH ALL IN order_list_cursor; FETCH ALL IN page_count_cursor;COMMIT;
>
> Server version is 9.1, both 9.1 & 9.2 psql works, C API version is 9.2
>
> Any ideas ?
>
> Thanks in advance
> Yao
>

[GENERAL] Weird "function does not exist" error.

[邓尧]

Hi,

I ran into a "function  does not exist" error when invoking a function
created by myself. What's weird is that the function works perfectly if I
invoke it through the command line tool "psql" but fails if I invoke it
through the C API.

The SQL to create the function is:
CREATE OR REPLACE FUNCTION "lo_select_page"(uid_ BIGINT, lottery_type_ INT,
type_ INT, time_start_ BIGINT, time_end_ BIGINT,
page_number_ BIGINT, prize_status_ INT)
RETURNS SETOF REFCURSOR AS $$
...
END $$ LANGUAGE plpgsql;

The SQL to invoke the function:
BEGIN;SELECT lo_select_page(4163481095, 0, 0, 0, 189347760, 0, 0);FETCH
ALL IN order_list_cursor; FETCH ALL IN page_count_cursor;COMMIT;

Server version is 9.1, both 9.1 & 9.2 psql works, C API version is 9.2

Any ideas ?

Thanks in advance
Yao

Re: [GENERAL] Duplicated entries are not ignored even if a "do instead nothing" rule is added.

[邓尧]

On Mon, Jan 9, 2012 at 9:53 AM, Jon Nelson wrote:

> On Sun, Jan 8, 2012 at 7:13 PM, 邓尧  wrote:
> > I have enabled the autocommit feature of psycopg2, and removed all the
> > transactions in source code, also changed the sql statement to the
> > following:
> >
> > insert into ACCOUNT(HOME)
> > select "v1" as HOME
> > where not exists (select 1 from ACCOUNT where HOME = "v1")
> >
> > Surprisingly, I still got the unique constraint violation error, but much
> > fewer than before ( only 3 during the last 3 days).
> > Any suggestions? Could it be a bug in psycopg2 or postgresql?
>
> Presumably, you need to add a distinct.
> insert into ... select DISTINCT  where not exists.
>

I don't think so. The the statement "select .. as .. where not exists" will
never return more than one row.


>
>
> --
> Jon
>

Re: [GENERAL] Duplicated entries are not ignored even if a "do instead nothing" rule is added.

[邓尧]

I have enabled the autocommit feature of psycopg2, and removed all the
transactions in source code, also changed the sql statement to the
following:

insert into ACCOUNT(HOME)
select "v1" as HOME
where not exists (select 1 from ACCOUNT where HOME = "v1")

Surprisingly, I still got the unique constraint violation error, but much
fewer than before ( only 3 during the last 3 days).
Any suggestions? Could it be a bug in psycopg2 or postgresql?

Thanks
-Yao

On Wed, Jan 4, 2012 at 1:11 AM, Merlin Moncure  wrote:

> On Tue, Jan 3, 2012 at 1:42 AM, Alban Hertroys  wrote:
> > On 3 Jan 2012, at 5:20, 邓尧 wrote:
> >
> >> Hi,
> >>
> >> I'm new to pgsql, I need the do something like the "INSERT IGNORE" in
> mysql. After some searching I got a solution, which is adding a "do instead
> nothing" rule to the corresponding table, but it fails sometimes.
> >
> > Yeah, if a concurrent transaction tries to create the same record, one
> of the transactions is going to find that it already exists on transaction
> commit. An INSERT-rule is not going to protect you against that.
>
> It will if you lock the table first in the same transaction...note
> this will greatly hurt concurrency and you have to watch for
> deadlocks.
>
> INSERT...SELECT..WHERE is going to be vastly superior to a rule based
> approach obviously.
>
> merlin
>

[GENERAL] Duplicated entries are not ignored even if a "do instead nothing" rule is added.

[邓尧]

Hi,

I'm new to pgsql, I need the do something like the "INSERT IGNORE" in
mysql. After some searching I got a solution, which is adding a "do instead
nothing" rule to the corresponding table, but it fails sometimes.
The table and the rule is created with the following sql statements:

create sequence ACCOUNT_ID_SEQ;
create table ACCOUNT (
ID bigint primary key default nextval('ACCOUNT_ID_SEQ'),
HOME char(255) not null,
NAME char(255)
);
create unique index on ACCOUNT(HOME);
create index on ACCOUNT(NAME);

create rule IGNORE_DUPLICATED_ACCOUNT
as on insert to ACCOUNT
where exists(select 1 from ACCOUNT where HOME = NEW.HOME)
do instead nothing;

There are about 20 clients do the following insertion (no UPDATE, some of
them might DELETE):
begin transaction:
insert into ACCOUNT(HOME) values (v1);
insert into ACCOUNT(HOME) values (v2);
...
commit;

Sometimes I got the error says the unique constraint "account_home_idx" is
violated.

Any suggestions?

Thanks
-Yao