Re: [GENERAL] Lifetime of PQexecPrepared() returned value

[Tom Lane]

Igor Korot  writes:
> I have a following piece of code:

> [code]
> PGresult *res = PQexecPrepared();
> status = PQresultStatue( res );
> if( status == PGRES_TUPLES_OK )
> {
> for( int j = 0; j < PQntuples( res ); j++ )
> {
> char *foo = PQgetValue( res, j, 0 );
> char *bar = PQgetValue( res, j, 1 );
> MyObject *obj = new MyObject( foo, bar );
> if( SetAdditionalProperties( obj ) )
> {
>   result = 1;
>   break;
> }
> }
> PQclear( res );
> }

What I'm wondering about is whether the MyObject constructor is making
copies of the strings it's passed, or whether it thinks it can just hold
onto those pointers.  The pointers would be dangling once you do PQclear.
Now, if the MyObject has gone out of scope and been destroyed, which
this coding suggests would happen, then that shouldn't matter ... but
maybe the pointers got copied to somewhere longer-lived?  Anyway, there's
nothing visibly wrong with what you showed us, so the problem is somewhere
else.

regards, tom lane


-- 
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] Lifetime of PQexecPrepared() returned value

[Igor Korot]

Hi, Michael,

On Fri, Aug 4, 2017 at 3:26 PM, Michael Paquier
 wrote:
> On Fri, Aug 4, 2017 at 9:12 PM, Igor Korot  wrote:
>> Am I missing something? How do I fix the crash?
>
> Based on what I can see here, I see nothing wrong. Now it is hard to
> reach any conclusion with the limited information you are providing.

Same here.
I will give the full code when I get home.

Thank you.

> --
> Michael


-- 
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] Lifetime of PQexecPrepared() returned value

[Michael Paquier]

On Fri, Aug 4, 2017 at 9:12 PM, Igor Korot  wrote:
> Am I missing something? How do I fix the crash?

Based on what I can see here, I see nothing wrong. Now it is hard to
reach any conclusion with the limited information you are providing.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Lifetime of PQexecPrepared() returned value

[Igor Korot]

 Hi, ALL,
I have a following piece of code:

[code]
PGresult *res = PQexecPrepared();
status = PQresultStatue( res );
if( status == PGRES_TUPLES_OK )
{
for( int j = 0; j < PQntuples( res ); j++ )
{
char *foo = PQgetValue( res, j, 0 );
char *bar = PQgetValue( res, j, 1 );
MyObject *obj = new MyObject( foo, bar );
if( SetAdditionalProperties( obj ) )
{
  result = 1;
  break;
}
}
PQclear( res );
}

int SetAdditionalProperties(MyObject )
{
// a call to PQexecParams() here
}
[/code]

If the call to SetAdditionalProperties() fails, I get a crash on
PQclear(), stating that
the pointer is not allocated.

Am I missing something? How do I fix the crash?

Thank you.


-- 
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] hot standby questions

[armand pirvu]

Thank you guys

I think I kinda shot myself in the foot. I took a look at the wiki and I put in 
-x and so far so good

— Armand



> On Aug 3, 2017, at 11:03 PM, Lucas Possamai  wrote:
> 
> 
> 
> 2017-08-04 5:58 GMT+12:00 Jeff Janes  >:
> On Wed, Aug 2, 2017 at 8:19 PM, armand pirvu  > wrote:
> 
> Hi
> 
> Just trying to put together the hot_standby setup
> All docs I read are pointing to use as prefered method to use pg_basebackup 
> to set the base
> So far so good
> But
> 
> psql postgres -c "select pg_start_backup('backup')"
> pg_basebackup -D /var/lib/pgsql/sample  -Ft -z -P
> psql postgres -c "select pg_stop_backup()"
> 
> pg_basebackup does the equivalent of pg_start_backup and pg_stop_backup for 
> you.  It is not helpful, and might even sometimes be harmful, to do them 
> yourself when using pg_basebackup.
>  
> 
> Pretty much every where I looked at -x is not mentioned to be used
> 
> So what gives ? What did I miss ? It's gotta be soomething
> 
> That certainly isn't my experience.  If you find sites that don't mention -x, 
> -X, or --xlog-method, then I would be reluctant to take any of that site's 
> other advice seriously.  
> 
> But note that in version 10, -x will go away and the default will be changed 
> so that not specifying anything will be the same as -X stream.  perhaps you 
> are reading advice aimed at a future version.
> 
> Cheers,
> 
> Jeff
> 
> 
> I use pg_basebackup every day and the way I do is:
> 
> pg_basebackup -D /destination --checkpoint=fast --xlog-method=stream
> 
> The --xlog-method=stream option will copy the wal_files as well (to pg_xlogs 
> directory).
> 
> That works pretty well for me. You can either add the compress option too. 
> Hope this was helpful.
> 
> Cheers,
> Lucas

Re: [GENERAL] Do not INSERT if UPDATE fails

[Alexander Farber]

Hello, I have followed David's suggestion (thank you!) -

On Wed, Aug 2, 2017 at 6:40 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Aug 2, 2017 at 8:58 AM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>> However if the user record is not found or the user already has vip_until
>> >= CURRENT_TIMESTAMP (i.e. the user has already purchased "vip status") I
>> would like to cancel the INSERT.
>>
>>
> ​You can "join" two DDL commands by using a Common Table Expression (CTE)
> (i.e., WITH / SELECT)​.  You would need to make it so the UPDATE happens
> first and if there are no results the INSERT simply becomes a no-op.
>
>
and the following works (if I change the function return type to VOID):

CREATE OR REPLACE FUNCTION words_buy_vip(
in_sid text,
in_social integer,
in_tid text,
in_item text,
in_price float,
in_ip inet)
RETURNS void AS
$func$
WITH cte AS (
UPDATEwords_users u
SET   vip_until = CURRENT_TIMESTAMP + interval '1 year'
FROM  words_social s
WHERE s.sid= in_sid
AND   s.social = in_social
AND   u.uid= s.uid
AND   (u.vip_until IS NULL OR u.vip_until <
CURRENT_TIMESTAMP)
RETURNING
  u.uid AS uid,
  in_sidAS sid,
  in_social AS social,
  in_tidAS tid,
  in_price  AS price,
  in_ip AS ip
)
INSERT INTO words_payments (
sid,
social,
tid,
paid,
price,
ip
) SELECT
sid,
social,
tid,
CURRENT_TIMESTAMP,
price,
ip
FROM cte
-- RETURNING uid;

$func$ LANGUAGE sql;

But I wonder how to return the uid in the above statement?

(my original function returned integer uid)

Regards
Alex

Re: [GENERAL] cast issue in WITH RECURION

[k b]

  > when i
  create a recursive query and try to
 add the distances i get
  a message:
  > ERROR:  recursive query
  "edges" column 3 has type numeric(7,3)
 in
  non-recursive term but type numeric
 overall.
  
  > My exercise is almost
  identical to the example in the docs:
  >
  WITH RECURSIVE search_graph(id, link,
 data, depth, path,
  cycle) AS (
  >        SELECT g.id,
  g.link, g.data, 1,
  >         
  ARRAY[g.id],
  >          false
  >        FROM graph g
  >      UNION ALL
  > 
        SELECT g.id, g.link, 
  >     
    sg.data + g.data, -- altered
 section, data is
  numeric(7,3)
  >        sg.depth +
  1,
  >          path || g.id,
  >          g.id = ANY(path)
  >        FROM graph g,
 search_graph
  sg
  >        WHERE g.id = sg.link
 AND
  NOT cycle
  > )
  > SELECT
  * FROM search_graph;
  
  I believe the solution is rather
 simple; just
  cast(sg.data + g.data to
 numeric(7,3))
  
  Alban Hertroys
  
 
 --
Please ignore that last message from me, it does work. Thank you for the answer!
 Karl


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general