date:20150626

Hi,

Apologies if I am being incredibly stupid, but I just can't seem to
get this to work for me.

I have a function that validates a web session is still active, so my
code looks something like this :

BEGIN
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
SAVEPOINT sp_cleanedSessionTable;
select * into strict v_row from app_security.app_val_session_vw where
session_id=p_session_id and session_ip=p_client_ip and
session_user_agent=p_user_agent;
update app_security.app_sessions set session_lastactive=v_now where
session_id=p_session_id;
etc. etc.
END


app_security.cleanSessionTable works beautifully on its on, i.e. give
TTL values and it deletes the appropriate roles from the session table
etc.

However, when used in conjunction with the broader validateSession
function,  whatever cleanSessionTable does gets rolledback because
obviously the select/update statements don't work because cleanSession
table has deleted the expired session ?

As you can see, I've tried adding a savepoint, but this seems to have
no effect ?  The autorollback still re-instates the expired session.

Help !

Thanks

Tim


-- 
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] Functions, savepoints, autocommit = I am confused !

On Fri, Jun 26, 2015 at 9:38 AM, Tim Smith 
wrote:

> I have a function that validates a web session is still active, so my
> code looks something like this :
>
> BEGIN
> perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
> SAVEPOINT sp_cleanedSessionTable;
> select * into strict v_row from app_security.app_val_session_vw where
> session_id=p_session_id and session_ip=p_client_ip and
> session_user_agent=p_user_agent;
> update app_security.app_sessions set session_lastactive=v_now where
> session_id=p_session_id;
> etc. etc.
> END
>
> However, when used in conjunction with the broader validateSession
> function,  whatever cleanSessionTable does gets rolledback because
> obviously the select/update statements don't work because cleanSession
> table has deleted the expired session ?
>
> As you can see, I've tried adding a savepoint, but this seems to have
> no effect ?  The autorollback still re-instates the expired session.


You need to trap exceptions and in the handler block issue a

ROLLBACK TO SAVEPOINT

http://www.postgresql.org/docs/9.4/static/sql-rollback-to.html


otherwise the the ROLLBACK issued at pg-session end will simply rollback
everything.

David J.

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !

On 06/26/2015 06:38 AM, Tim Smith wrote:

Hi,

Apologies if I am being incredibly stupid, but I just can't seem to
get this to work for me.

I have a function that validates a web session is still active, so my
code looks something like this :

BEGIN
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
SAVEPOINT sp_cleanedSessionTable;
select * into strict v_row from app_security.app_val_session_vw where
session_id=p_session_id and session_ip=p_client_ip and
session_user_agent=p_user_agent;
update app_security.app_sessions set session_lastactive=v_now where
session_id=p_session_id;
etc. etc.
END

So this is in a plpgsql function?

If so see here:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

40.6.6. Trapping Errors

app_security.cleanSessionTable works beautifully on its on, i.e. give
TTL values and it deletes the appropriate roles from the session table
etc.

However, when used in conjunction with the broader validateSession
function, whatever cleanSessionTable does gets rolledback because
obviously the select/update statements don't work because cleanSession
table has deleted the expired session ?

Where is the validateSession function?

More to the point, can you show how it is used in conjunction with?

As you can see, I've tried adding a savepoint, but this seems to have
no effect ? The autorollback still re-instates the expired session.

See the plpgsql link above.

Help !

Thanks

Tim

--
Adrian Klaver
adrian.kla...@aklaver.com

--
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] Functions, savepoints, autocommit = I am confused !

> So this is in a plpgsql function?

It is yes, but I thought I would spare you a copy/paste of the entire thing.

The error trapping section currently looks like this :
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to validate
session for session % (SQLSTATE: %  - SQLERRM: %)',
session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured
(sval fail)';
END;

>
> Where is the validateSession function?
>
> More to the point, can you show how it is used in conjunction with?
>

The validateSession function was the one I pasted ?  Do you mean you
want to see the actual function() definition at the top ?

The cleanSession function (the one validateSession calls at the top)
is simple (the v_ values are simply 'epoch minus TTL') :
BEGIN
delete from app_security.app_sessions where
session_start<=v_forcedTimeout or
session_lastactive<=v_sessionTimeout;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION
USING ERRCODE = sqlstate,
MESSAGE = 'Failed to clean session table (' || sqlerrm || ')',
HINT = 'Database error(sclean fail)';
END;
$$ LANGUAGE plpgsql;

>
> See the plpgsql link above.


Will take a look at the link.  Thanks !


-- 
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] Functions, savepoints, autocommit = I am confused !

> You need to trap exceptions and in the handler block issue a
>
> ROLLBACK TO SAVEPOINT
>
> http://www.postgresql.org/docs/9.4/static/sql-rollback-to.html
>
>
> otherwise the the ROLLBACK issued at pg-session end will simply rollback
> everything.
>
> David J.
>

Thanks, will take a look.


-- 
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] Functions, savepoints, autocommit = I am confused !

Hi David,

I should have perhaps made clear this was a saved function, so my
understanding is ROLLBACK can't be used as its implicit.


-- 
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] Functions, savepoints, autocommit = I am confused !

On Fri, Jun 26, 2015 at 10:48 AM, Tim Smith 
wrote:

> Hi David,
>
> I should have perhaps made clear this was a saved function, so my
> understanding is ROLLBACK can't be used as its implicit.
>

I am pretty certain "ROLLBACK" cannot be used but the "ROLLBACK TO
SAVEPOINT" can - they are and do two different things.  If you can issue a
savepoint inside a stored function it would stand to reason you must be
able to rollback to that named savepoint from within the same - nothing
external would even known about it.

David J.

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !


On 06/26/2015 07:24 AM, Tim Smith wrote:

So this is in a plpgsql function?


It is yes, but I thought I would spare you a copy/paste of the entire thing.

The error trapping section currently looks like this :
EXCEPTION
 WHEN OTHERS THEN
 RAISE EXCEPTION 'Failed to validate
session for session % (SQLSTATE: %  - SQLERRM: %)',
session_id,SQLSTATE,SQLERRM
 USING HINT = 'Database error occured
(sval fail)';
END;



Where is the validateSession function?

More to the point, can you show how it is used in conjunction with?



The validateSession function was the one I pasted ?  Do you mean you
want to see the actual function() definition at the top ?

The cleanSession function (the one validateSession calls at the top)


Well what you showed before was cleanSessionTable, are we talking the 
same thing?



is simple (the v_ values are simply 'epoch minus TTL') :
BEGIN
delete from app_security.app_sessions where
session_start<=v_forcedTimeout or
session_lastactive<=v_sessionTimeout;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION
USING ERRCODE = sqlstate,
MESSAGE = 'Failed to clean session table (' || sqlerrm || ')',
HINT = 'Database error(sclean fail)';
END;
$$ LANGUAGE plpgsql;



See the plpgsql link above.



Will take a look at the link.  Thanks !





--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Functions, savepoints, autocommit = I am confused !

Adrian,

Ok, let's start fresh.

app_security.validateSession() calls app_security.cleanSessionTable().

app_security.cleanSessionTable(), when called on its, own, does not
cause me any issues.  It operates as designed.

I have added ROLLBACK TO SAVEPOINT to validateSession(), so that it now reads :

CREATE FUNCTION app_security.validateSession(p_session_id
app_domains.app_uuid,p_client_ip inet,p_user_agent
text,p_forcedTimeout bigint,p_sessionTimeout bigint) RETURNS json AS
$$
DECLARE
v_now bigint;
v_row app_security.app_val_session_vw%ROWTYPE;
v_json json;
BEGIN
v_now := extract(epoch FROM now())::bigint;
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
SAVEPOINT sp_cleanedSessionTable;
select * into strict v_row from app_security.app_val_session_vw where
session_id=p_session_id and session_ip=p_client_ip and
session_user_agent=p_user_agent;
update app_security.app_sessions set session_lastactive=v_now where
session_id=p_session_id;
select row_to_json(v_row) into v_json ;
return v_json;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO SAVEPOINT sp_cleanedSessionTable;
RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: %
 - SQLERRM: %)', session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval fail)';
END;
$$ LANGUAGE plpgsql;


Calling the function yields the following :

ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function
app_security.validatesession(app_domains.app_uuid,inet,text,bigint,bigint)
line 16 at SQL statement


Line 16 to which it refers is "ROLLBACK TO SAVEPOINT"


-- 
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] Functions, savepoints, autocommit = I am confused !

2015-06-26 Thread Holger.Friedrich-Fa-Trivadis

Tim Smith wrote on Friday, June 26, 2015 5:38 PM:
> ERROR:  cannot begin/end transactions in PL/pgSQL
> HINT:  Use a BEGIN block with an EXCEPTION clause instead.
> CONTEXT:  PL/pgSQL function
> app_security.validatesession(app_domains.app_uuid,inet,text,bigint,bigint)
> line 16 at SQL statement

> Line 16 to which it refers is "ROLLBACK TO SAVEPOINT"

I believe I've read you can have nested BEGIN ... END blocks, and the 
transaction control is done implicitly by the PL/pgSQL exception handling, so 
you probably can write

BEGIN
...
BEGIN
...
EXCEPTION
WHEN OTHERS THEN
...
END
END;

which would (hopefully) only roll back the second ... not the first ... (not 
sure if you still need to declare the savepoint, at least, as you found out, 
explicitly rolling back to the savepoint is not allowed in PL/pgSQL).  Note 
that the third ... probably should not raise or re-raise an exception, 
otherwise you have an exception in the outer BEGIN-END block and everything is 
rolled back.

Best regards
Holger Friedrich

-- 
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] Functions, savepoints, autocommit = I am confused !


On 06/26/2015 08:38 AM, Tim Smith wrote:

Adrian,

Ok, let's start fresh.

app_security.validateSession() calls app_security.cleanSessionTable().

app_security.cleanSessionTable(), when called on its, own, does not
cause me any issues.  It operates as designed.

I have added ROLLBACK TO SAVEPOINT to validateSession(), so that it now reads :

CREATE FUNCTION app_security.validateSession(p_session_id
app_domains.app_uuid,p_client_ip inet,p_user_agent
text,p_forcedTimeout bigint,p_sessionTimeout bigint) RETURNS json AS
$$
DECLARE
v_now bigint;
v_row app_security.app_val_session_vw%ROWTYPE;
v_json json;
BEGIN
v_now := extract(epoch FROM now())::bigint;
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
SAVEPOINT sp_cleanedSessionTable;
select * into strict v_row from app_security.app_val_session_vw where
session_id=p_session_id and session_ip=p_client_ip and
session_user_agent=p_user_agent;
update app_security.app_sessions set session_lastactive=v_now where
session_id=p_session_id;
select row_to_json(v_row) into v_json ;
return v_json;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO SAVEPOINT sp_cleanedSessionTable;
RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: %
  - SQLERRM: %)', session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval fail)';
END;
$$ LANGUAGE plpgsql;


Calling the function yields the following :

ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function
app_security.validatesession(app_domains.app_uuid,inet,text,bigint,bigint)
line 16 at SQL statement


Line 16 to which it refers is "ROLLBACK TO SAVEPOINT"


Well AFAIK, you can not explicitly use SAVEPOINT in plpgsql as the 
EXCEPTION handling is implicitly using it.


Still not quite sure what you want.

Are you looking to catch any exception coming from cleanSessionTable and 
then abort the 'select into * .." section?









--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Functions, savepoints, autocommit = I am confused !

On Fri, Jun 26, 2015 at 11:38 AM, Tim Smith 
wrote:

> Adrian,
>
> Ok, let's start fresh.
>
> app_security.validateSession() calls app_security.cleanSessionTable().
>
> app_security.cleanSessionTable(), when called on its, own, does not
> cause me any issues.  It operates as designed.
>
> I have added ROLLBACK TO SAVEPOINT to validateSession(), so that it now
> reads :
>
> CREATE FUNCTION app_security.validateSession(p_session_id
> app_domains.app_uuid,p_client_ip inet,p_user_agent
> text,p_forcedTimeout bigint,p_sessionTimeout bigint) RETURNS json AS
> $$
> DECLARE
> v_now bigint;
> v_row app_security.app_val_session_vw%ROWTYPE;
> v_json json;
> BEGIN
> v_now := extract(epoch FROM now())::bigint;
> perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
> SAVEPOINT sp_cleanedSessionTable;
> select * into strict v_row from app_security.app_val_session_vw where
> session_id=p_session_id and session_ip=p_client_ip and
> session_user_agent=p_user_agent;
> update app_security.app_sessions set session_lastactive=v_now where
> session_id=p_session_id;
> select row_to_json(v_row) into v_json ;
> return v_json;
> EXCEPTION
> WHEN OTHERS THEN
> ROLLBACK TO SAVEPOINT sp_cleanedSessionTable;
> RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: %
>  - SQLERRM: %)', session_id,SQLSTATE,SQLERRM
> USING HINT = 'Database error occured (sval fail)';
> END;
> $$ LANGUAGE plpgsql;
>
>
> Calling the function yields the following :
>
> ERROR:  cannot begin/end transactions in PL/pgSQL
> HINT:  Use a BEGIN block with an EXCEPTION clause instead.
> CONTEXT:  PL/pgSQL function
> app_security.validatesession(app_domains.app_uuid,inet,text,bigint,bigint)
> line 16 at SQL statement
>
>
> Line 16 to which it refers is "ROLLBACK TO SAVEPOINT"
>
>
I may have led you astray here - though from what you've described (no
checking on my end) apparently the SAVEPOINT is processed and silently
ignored when it seems like it should give the same error as you get when
trying to invoke ROLLBACK TO SAVEPOINT.

David J.

[GENERAL] Foreign data wrappers and indexes on remote side

2015-06-26 Thread Filip Rembiałkowski

Hi.

Is there any way to take use of indexes on foreign tables?

Currently (at least with tds_fdw, that I was testing) the planner just
does a dumb full sequential scan in all cases.

That is

SELECT drink FROM foreignbar;

-- takes as much time as

SELECT drink FROM foreignbar where drink_key = 3210;


Thanks!


-- 
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] Functions, savepoints, autocommit = I am confused !

Adrian,

"what I want" is quite simple, I want the function to work as intended.  ;-)

Let's step through the function :

(1) perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);

Function calls cleanSessionTable.cleanSessionTable is simple.  It
calls DELETE on the session table using epochs as filters.That's
fine, it works, I've tested that function.

The reason I want cleanSessionTable called here is because this is the
back-end to a web app.  This function is called "validateSession",
hence it needs to do what it says on the tin and make sure expired
sessions are not validated.

The problem happens next 

(2) select * into strict v_row  .etc

IF cleanSessionTable deleted the row, then this select will fail.
Which is fine ... EXCEPT for the fact that Postgresql will then
roll-back the good work it did on the previous statement
(cleanSessionTable).

I want the deleted session rows to remain deleted.  I don't want them back.


-- 
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] Foreign data wrappers and indexes on remote side

2015-06-26 Thread Tom Lane

=?UTF-8?Q?Filip_Rembia=C5=82kowski?=  writes:
> Is there any way to take use of indexes on foreign tables?

> Currently (at least with tds_fdw, that I was testing) the planner just
> does a dumb full sequential scan in all cases.

That would be something to discuss with the author of tds_fdw.  It's
mostly on the head of each individual FDW how smart plans for it will be.

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] Foreign data wrappers and indexes on remote side

On Fri, Jun 26, 2015 at 12:09 PM, Filip Rembiałkowski <
filip.rembialkow...@gmail.com> wrote:

> Hi.
>
> Is there any way to take use of indexes on foreign tables?
>
> Currently (at least with tds_fdw, that I was testing) the planner just
> does a dumb full sequential scan in all cases.
>
> That is
>
> SELECT drink FROM foreignbar;
>
> -- takes as much time as
>
> SELECT drink FROM foreignbar where drink_key = 3210;
>

It is possible but implementation is the responsibility of the FDW
middleware authors.

Since "tds_fdw" is not core this would not be the correct forum.

David J.

Re: [GENERAL] Functions, savepoints, autocommit = I am confused !


On 06/26/2015 09:08 AM, Tim Smith wrote:

Adrian,

"what I want" is quite simple, I want the function to work as intended.  ;-)


Well that was my problem, I did not know what was intended.



Let's step through the function :

(1) perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);

Function calls cleanSessionTable.cleanSessionTable is simple.  It
calls DELETE on the session table using epochs as filters.That's
fine, it works, I've tested that function.

The reason I want cleanSessionTable called here is because this is the
back-end to a web app.  This function is called "validateSession",
hence it needs to do what it says on the tin and make sure expired
sessions are not validated.

The problem happens next 

(2) select * into strict v_row  .etc

IF cleanSessionTable deleted the row, then this select will fail.
Which is fine ... EXCEPT for the fact that Postgresql will then
roll-back the good work it did on the previous statement
(cleanSessionTable).

I want the deleted session rows to remain deleted.  I don't want them back.


Two options that I can see if I am following correctly:

1) Look before you leap

Before this:
update app_security.app_sessions set session_lastactive=v_now where
session_id=p_session_id;

Do:
In the DECLARE
ct_var integer;

select count(*) into ct_var from app_security.app_sessions where 
session_id=p_session_id


and then use IF on the ct_var to either UPDATE if cat_var > 0 or just 
pass if = 0


2) Act and then ask for forgiveness.

You can have more then one BEGIN/END block in plpgsql. So you could put 
the update in its own block and catch the exception there. See:


http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Example 40-2. Exceptions with UPDATE/INSERT









--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Functions, savepoints, autocommit = I am confused !

> Well that was my problem, I did not know what was intended.

apt-get install mind-reading   ;-)

> 1) Look before you leap
>

I'm confused by this option ?

My script reads as follows :
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
select * into strict v_row from app_security.app_val_session_vw where
session_id=p_session_id and session_ip=p_client_ip and
session_user_agent=p_user_agent;
update app_security.app_sessions set session_lastactive=v_now where
session_id=p_session_id;


Its the "select * into strict" that's causing me grief, not the
"update",  isn't it ?!?



>
> 2) Act and then ask for forgiveness.
>
> You can have more then one BEGIN/END block in plpgsql. So you could put the
> update in its own block and catch the exception there.

I'll give that a go.  I thought it might be an option, but it was not
too clear from the docs whether the sub-blocks were treated as
seperate transactions when used within a function.


-- 
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] Functions, savepoints, autocommit = I am confused !

Me again, I've reworded it, but its still rolling back !!!

Using the code below, if I call :
select app_security.validateSession('XYZ','10.10.123.43','Z',5,5);

I get an error raised on the select that follows cleanSessionTable.
Which is fine.
BUT, Postgresql is still rolling back !

If I go back afterwards and say
select app_security.validateSession('XYZ','10.10.123.43','Z',5,5);

I get the session data shown to me again ?


CREATE FUNCTION app_security.validateSession(p_session_id
app_domains.app_uuid,p_client_ip inet,p_user_agent
text,p_forcedTimeout bigint,p_sessionTimeout bigint) RETURNS json AS
$$
DECLARE
v_now bigint;
v_row app_security.app_val_session_vw%ROWTYPE;
v_json json;
BEGIN
v_now := extract(epoch FROM now())::bigint;
BEGIN
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to clean session table % (SQLSTATE: %  -
SQLERRM: %)', session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval cleansess fail)';
END;
select * into strict v_row from app_security.app_val_session_vw where
session_id=p_session_id and session_ip=p_client_ip and
session_user_agent=p_user_agent;
update app_security.app_sessions set session_lastactive=v_now where
session_id=p_session_id;
select row_to_json(v_row) into v_json ;
return v_json;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: %
 - SQLERRM: %)', session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval fail)';
END;
$$ LANGUAGE plpgsql;


-- 
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] Functions, savepoints, autocommit = I am confused !


On 06/26/2015 09:54 AM, Tim Smith wrote:

Well that was my problem, I did not know what was intended.


apt-get install mind-reading   ;-)


1) Look before you leap



I'm confused by this option ?

My script reads as follows :
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
select * into strict v_row from app_security.app_val_session_vw where
session_id=p_session_id and session_ip=p_client_ip and
session_user_agent=p_user_agent;
update app_security.app_sessions set session_lastactive=v_now where
session_id=p_session_id;


Its the "select * into strict" that's causing me grief, not the
"update",  isn't it ?!?


Did not see the strict. In any case I thought cleanSessionTable was 
cleaning out app_security.app_sessions not app_security.app_val_session_vw.


Assuming something else is going you have two options(sense a theme?):

1) Remove the strict and do as I suggested in the previous post.

2) Move the count and IF before the select * into .. and then do what 
you want.








2) Act and then ask for forgiveness.

You can have more then one BEGIN/END block in plpgsql. So you could put the
update in its own block and catch the exception there.


I'll give that a go.  I thought it might be an option, but it was not
too clear from the docs whether the sub-blocks were treated as
seperate transactions when used within a function.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Functions, savepoints, autocommit = I am confused !


On 06/26/2015 10:02 AM, Tim Smith wrote:

Me again, I've reworded it, but its still rolling back !!!

Using the code below, if I call :
select app_security.validateSession('XYZ','10.10.123.43','Z',5,5);

I get an error raised on the select that follows cleanSessionTable.
Which is fine.
BUT, Postgresql is still rolling back !

If I go back afterwards and say
select app_security.validateSession('XYZ','10.10.123.43','Z',5,5);

I get the session data shown to me again ?


CREATE FUNCTION app_security.validateSession(p_session_id
app_domains.app_uuid,p_client_ip inet,p_user_agent
text,p_forcedTimeout bigint,p_sessionTimeout bigint) RETURNS json AS
$$
DECLARE
v_now bigint;
v_row app_security.app_val_session_vw%ROWTYPE;
v_json json;
BEGIN
v_now := extract(epoch FROM now())::bigint;
BEGIN
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to clean session table % (SQLSTATE: %  -
SQLERRM: %)', session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval cleansess fail)';
END;
select * into strict v_row from app_security.app_val_session_vw where
session_id=p_session_id and session_ip=p_client_ip and
session_user_agent=p_user_agent;
update app_security.app_sessions set session_lastactive=v_now where
session_id=p_session_id;
select row_to_json(v_row) into v_json ;
return v_json;
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: %
  - SQLERRM: %)', session_id,SQLSTATE,SQLERRM


I would change the above to RAISE NOTICE. The EXCEPTION has already been 
raised. Re-raising it without an enclosing block to capture it will I am 
pretty sure abort/rollback the function/transaction.



USING HINT = 'Database error occured (sval fail)';
END;
$$ LANGUAGE plpgsql;





--
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] Re: Get the difference between two timestamp cells but in a special format in PostgreSQL

2015-06-26 Thread litu16

Hi Adrian,

but I would like to get the time diff in this format

0years 0months 0days 00:00:00.000

not only hours, minutes, seconds.

is this possible???

Thanks Advanced.



--
View this message in context: 
http://postgresql.nabble.com/Get-the-difference-between-two-timestamp-cells-but-in-a-special-format-in-PostgreSQL-tp5855074p5855215.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] Functions, savepoints, autocommit = I am confused !

>
> Did not see the strict. In any case I thought cleanSessionTable was cleaning
> out app_security.app_sessions not app_security.app_val_session_vw.

Yes.  cleanSessionTable does the actual cleaning.

The point of the select from app_security.app_val_session_vw is that
if the session is valid, the function returns a JSON containing
pertinent information relating to the session.


>
> Assuming something else is going you have two options(sense a theme?):
>
> 1) Remove the strict and do as I suggested in the previous post.
>
> 2) Move the count and IF before the select * into .. and then do what you
> want.
>

So are you saying I need to do both this counting stuff AND the "ask
for forgiveness",  I thought you were suggesting mutuallly exclusive
options earlier ?

I'll work on integrating the count stuff now, but I still don't
understand why a BEGIN subblock still gets rolled back.

This is on 9.4.4 if it makes any difference, by the way.


>>> 2) Act and then ask for forgiveness.
>>>

Regarding this part, I have changed to RAISE NOTICE and added a return
to the bottom of the Pl/PGSQL.

The function does not abort now, I get a simple :

NOTICE:  Failed to validate session for session XYZ (SQLSTATE: P0002
- SQLERRM: query returned no rows)
HINT:  Database error occured (sval fail)
 validatesession
-
 [false]
(1 row)


But the problem persists in that the delete still gets rolled back,
despite it being in its own sub block.

CREATE FUNCTION app_security.validateSession(p_session_id
app_domains.app_uuid,p_client_ip inet,p_user_agent
text,p_forcedTimeout bigint,p_sessionTimeout bigint) RETURNS json AS
$$
DECLARE
v_now bigint;
v_row app_security.app_val_session_vw%ROWTYPE;
v_json json;
BEGIN
v_now := extract(epoch FROM now())::bigint;
BEGIN
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to clean session table % (SQLSTATE: %  -
SQLERRM: %)', session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval cleansess fail)';
END;
select * into strict v_row from app_security.app_val_session_vw where
session_id=p_session_id and session_ip=p_client_ip and
session_user_agent=p_user_agent;
update app_security.app_sessions set session_lastactive=v_now where
session_id=p_session_id;
select row_to_json(v_row) into v_json ;
return v_json;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Failed to validate session for session % (SQLSTATE: %  -
SQLERRM: %)', p_session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval fail)';
return '[false]';
END;
$$ LANGUAGE plpgsql;


-- 
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] Re: Get the difference between two timestamp cells but in a special format in PostgreSQL


On 06/25/2015 08:23 PM, litu16 wrote:

Hi Adrian,

but I would like to get the time diff in this format

0years 0months 0days 00:00:00.000

not only hours, minutes, seconds.

is this possible???


Well age:

http://www.postgresql.org/docs/9.4/interactive/functions-datetime.html

does that sort of:

production=# SELECT age(timestamp '2015-06-26 09:15:15', timestamp 
'2015-06-26 02:16:00');

   age
--
 06:59:15
(1 row)

production=# SELECT age(timestamp '2015-06-26 09:15:15', timestamp 
'2015-06-20 02:16:00');

   age
-
 6 days 06:59:15
(1 row)

production=# SELECT age(timestamp '2015-06-26 09:15:15', timestamp 
'2014-06-20 02:16:00');

  age

 1 year 6 days 06:59:15
(1 row)







Thanks Advanced.



--
View this message in context: 
http://postgresql.nabble.com/Get-the-difference-between-two-timestamp-cells-but-in-a-special-format-in-PostgreSQL-tp5855074p5855215.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Functions, savepoints, autocommit = I am confused !


On 06/26/2015 10:49 AM, Tim Smith wrote:


Did not see the strict. In any case I thought cleanSessionTable was cleaning
out app_security.app_sessions not app_security.app_val_session_vw.


Yes.  cleanSessionTable does the actual cleaning.

The point of the select from app_security.app_val_session_vw is that
if the session is valid, the function returns a JSON containing
pertinent information relating to the session.




Assuming something else is going you have two options(sense a theme?):

1) Remove the strict and do as I suggested in the previous post.

2) Move the count and IF before the select * into .. and then do what you
want.



So are you saying I need to do both this counting stuff AND the "ask
for forgiveness",  I thought you were suggesting mutuallly exclusive
options earlier ?


Yes, they are different ways of approaching the problem.


I'll work on integrating the count stuff now, but I still don't
understand why a BEGIN subblock still gets rolled back.

This is on 9.4.4 if it makes any difference, by the way.



2) Act and then ask for forgiveness.



Regarding this part, I have changed to RAISE NOTICE and added a return
to the bottom of the Pl/PGSQL.

The function does not abort now, I get a simple :

NOTICE:  Failed to validate session for session XYZ (SQLSTATE: P0002
- SQLERRM: query returned no rows)
HINT:  Database error occured (sval fail)
  validatesession
-
  [false]
(1 row)


But the problem persists in that the delete still gets rolled back,
despite it being in its own sub block.


I knew I was missing something:(
http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

"When an error is caught by an EXCEPTION clause, the local variables of 
the PL/pgSQL function remain as they were when the error occurred, but 
all changes to persistent database state within the block are rolled 
back. As an example, consider this fragment:"



So I would try
CREATE FUNCTION app_security.validateSession(p_session_id
app_domains.app_uuid,p_client_ip inet,p_user_agent
text,p_forcedTimeout bigint,p_sessionTimeout bigint) RETURNS json AS
$$
DECLARE
v_now bigint;
v_row app_security.app_val_session_vw%ROWTYPE;
v_json json;
BEGIN


BEGIN
perform app_security.cleanSessionTable(p_forcedTimeout,p_sessionTimeout);
EXCEPTION
WHEN OTHERS THEN
RAISE EXCEPTION 'Failed to clean session table % (SQLSTATE: %  -
SQLERRM: %)', session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval cleansess fail)';
END;

BEGIN
v_now := extract(epoch FROM now())::bigint;
select * into strict v_row from app_security.app_val_session_vw where
session_id=p_session_id and session_ip=p_client_ip and
session_user_agent=p_user_agent;
update app_security.app_sessions set session_lastactive=v_now where
session_id=p_session_id;
select row_to_json(v_row) into v_json ;
return v_json;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'Failed to validate session for session % (SQLSTATE: %  -
SQLERRM: %)', p_session_id,SQLSTATE,SQLERRM
USING HINT = 'Database error occured (sval fail)';
return '[false]';
END;

END;
$$ LANGUAGE plpgsql;

--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Functions, savepoints, autocommit = I am confused !

>
>
> I knew I was missing something:(
> http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>
> "When an error is caught by an EXCEPTION clause, the local variables of the
> PL/pgSQL function remain as they were when the error occurred, but all
> changes to persistent database state within the block are rolled back. As an
> example, consider this fragment:"
>
>
> So I would try



You are a genius.  ;-)

Thank you !

Have a delightful weekend.


-- 
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] Question about the isolation level and visible

2015-06-26 Thread Kevin Grittner

娄帅  wrote:

> I start two session with the following execute time order:
>
> session1:  START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>
> session2: INSERT INTO t1 values(1);
> session2: COMMIT;
>
> session1: SELECT * FROM t1;
>
> I found session1 got the value 1 which is inserted by session2.
>
> Is there any way to make session1 not see the value session2
> inserted？

This was recently clarified in the documentation by changing:

| [...] as of the start of the transaction, [...]

to:

| [...] as of the start of the first non-transaction-control
| statement in the transaction, [...]

We don't acquire the snapshot at START TRANSACTION time because it
may be necessary to acquire locks or set transaction properties
(e.g., READ ONLY) before the snapshot is acquired.  You could force
acquisition of a snapshot by running a short query (e.g., SELECT
1;) in session 1 before starting the transaction in session 2.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
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] Foreign data wrappers and indexes on remote side

2015-06-26 Thread Filip Rembiałkowski

Is WHERE clause push-down implemented in any known fdw?

Thank you.



On Fri, Jun 26, 2015 at 6:19 PM, Tom Lane  wrote:
> =?UTF-8?Q?Filip_Rembia=C5=82kowski?=  writes:
>> Is there any way to take use of indexes on foreign tables?
>
>> Currently (at least with tds_fdw, that I was testing) the planner just
>> does a dumb full sequential scan in all cases.
>
> That would be something to discuss with the author of tds_fdw.  It's
> mostly on the head of each individual FDW how smart plans for it will be.
>
> 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] Foreign data wrappers and indexes on remote side

On Fri, Jun 26, 2015 at 3:31 PM, Filip Rembiałkowski <
filip.rembialkow...@gmail.com> wrote:

> Is WHERE clause push-down implemented in any known fdw?
>

Google: postgresql fdw where clause push down

https://wiki.postgresql.org/wiki/SQL/MED#Open_questions

postgresql_fdw

http://www.postgresql.org/docs/9.4/interactive/postgres-fdw.html

David J.

[GENERAL] How to convert a text variable into a timestamp in postgreSQL?

2015-06-26 Thread litu16

I know how to convert a text to timestamp in postgreSQL using

*SELECT to_timestamp('05 Dec 2000', 'DD Mon ')*

but how can I convert a text variable (inside a function) to timestamp??

In my table (table_ebscb_spa_log04) "time" is a character varying column, in
which I have placed a formated date time (15-11-30 11:59:59.999 PM).
I have tried this function, in order to convert put the date time text into
a variable (it always change) and convert it into timestamp...

*CREATE OR REPLACE FUNCTION timediff()
  RETURNS trigger AS
$BODY$
DECLARE
timeascharvar character varying;
timeastistamp timestamp;

BEGIN
IF NEW.time_type = 'Lap' THEN 
  SELECT t.time FROM table_ebscb_spa_log04 t INTO timeascharvar;
  SELECT to_timestamp('timeascharvar', 'yy-mm-dd HH24:MI:SS.MS') INTO
timeastistamp;
END IF;
RETURN timeastistamp;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION timediff()
  OWNER TO postgres;*

but whenever I run it in the table, it shows this ERROR message...

 

It seems that "to_timestamp" waits for a number to be the year, how can I
get it to recognize the variable as if it were numbers?





--
View this message in context: 
http://postgresql.nabble.com/How-to-convert-a-text-variable-into-a-timestamp-in-postgreSQL-tp5855346.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] Question about CONSTRAINT TRIGGER

2015-06-26 Thread ZM Yang

Hi folks,

I'm confused about the usage of CONSTRAINT TRIGGER. More specifically, the
documentation says that the name of another table referenced by the
constraint can be specified in a FROM clause:

"The (possibly schema-qualified) name of another table referenced by the
constraint. This option is used for foreign-key constraints and is not
recommended for general use. This can only be specified for constraint
triggers."

(1)
How can the table name specified in the the FROM clause be used? It seems
that it cannot be referenced in the WHERE clause. (Please correct me if I'm
wrong)

(2)
How is the table specified in the FROM clause used for FK constraints?

Thanks,
ZM

Re: [GENERAL] How to convert a text variable into a timestamp in postgreSQL?

On Friday, June 26, 2015, litu16  wrote:

> I know how to convert a text to timestamp in postgreSQL using
>
> *SELECT to_timestamp('05 Dec 2000', 'DD Mon ')*
>
> but how can I convert a text variable (inside a function) to timestamp??
>
>
Generally, just try casting it.

textvarname::date

David J.

Re: [GENERAL] Question about CONSTRAINT TRIGGER

On Friday, June 26, 2015, ZM Yang  wrote:

> Hi folks,
>
> I'm confused about the usage of CONSTRAINT TRIGGER. More specifically, the
> documentation says that the name of another table referenced by the
> constraint can be specified in a FROM clause:
>
> "The (possibly schema-qualified) name of another table referenced by the
> constraint. This option is used for foreign-key constraints and is not
> recommended for general use. This can only be specified for constraint
> triggers."
>
> (1)
> How can the table name specified in the the FROM clause be used? It seems
> that it cannot be referenced in the WHERE clause. (Please correct me if I'm
> wrong)
>
> (2)
> How is the table specified in the FROM clause used for FK constraints?
>
> Thanks,
> ZM
>

I presume these are used by the system to choose between different triggers
based upon which PK table caused the on update or on delete cascade to
start.  Think of it as another kind of when clause.

David J.

[GENERAL] use null or 0 in foreign key column, to mean "no value"?

2015-06-26 Thread Robert Nikander

Hi,

(Maybe my subject line should be: `is not distinct from` and indexes.)

In Postgres 9.4, I’ve got a table of ‘items’ that references a table ‘colors’.  
Not all items have colors, so I created a nullable column in items like:

  color_id bigint references colors  

There is also an index on color_id:

  create index on items (color_id);

I thought this was the right way to do it, but now I’m not so sure... In 
application code, prepared statements want to say: `select * from items where 
color_id = ?` and that `?` might be a int or null, so that doesn’t work.  I 
used `is not distinct from` instead of =, which has the right meaning, but now 
I notice it doesn’t use the index for queries that replace `=` with `is not 
distinct from`, and queries run much slower.  Using `explain` confirms: it’s 
doing sequential scans where `=` was using index.

So… is this bad DB design to use null to mean that an item has no color? Should 
I instead put a special row in `colors`, maybe with id = 0, to represent the 
“no color” value?  Or is there some way to make an index work with nulls and 
`is not distinct from`?  

thank you,
Rob





-- 
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] How to convert a text variable into a timestamp in postgreSQL?


On 06/26/2015 11:41 AM, litu16 wrote:

I know how to convert a text to timestamp in postgreSQL using

*SELECT to_timestamp('05 Dec 2000', 'DD Mon ')*

but how can I convert a text variable (inside a function) to timestamp??

In my table (table_ebscb_spa_log04) "time" is a character varying column, in
which I have placed a formated date time (15-11-30 11:59:59.999 PM).
I have tried this function, in order to convert put the date time text into
a variable (it always change) and convert it into timestamp...

*CREATE OR REPLACE FUNCTION timediff()
   RETURNS trigger AS
 $BODY$
 DECLARE
 timeascharvar character varying;
 timeastistamp timestamp;

 BEGIN
 IF NEW.time_type = 'Lap' THEN
   SELECT t.time FROM table_ebscb_spa_log04 t INTO timeascharvar;
   SELECT to_timestamp('timeascharvar', 'yy-mm-dd HH24:MI:SS.MS') INTO


Lose the quotes on timeascharvar, it is a string already.

So:

SELECT to_timestamp(timeascharvar, 'yy-mm-dd HH24:MI:SS.MS')


timeastistamp;
 END IF;
 RETURN timeastistamp;
 END
 $BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
 ALTER FUNCTION timediff()
   OWNER TO postgres;*

but whenever I run it in the table, it shows this ERROR message...




I click on the link above I get:


PostgreSQL
File not found
Please contact Nabble Support if you need help.




It seems that "to_timestamp" waits for a number to be the year, how can I
get it to recognize the variable as if it were numbers?





--
View this message in context: 
http://postgresql.nabble.com/How-to-convert-a-text-variable-into-a-timestamp-in-postgreSQL-tp5855346.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] use null or 0 in foreign key column, to mean "no value"?

2015-06-26 Thread Jan de Visser

On June 26, 2015 11:59:05 PM Robert Nikander wrote:
> Hi,
> 
> (Maybe my subject line should be: `is not distinct from` and indexes.)
> 
> In Postgres 9.4, I’ve got a table of ‘items’ that references a table
> ‘colors’.  Not all items have colors, so I created a nullable column in
> items like:
> 
>   color_id bigint references colors
> 
> There is also an index on color_id:
> 
>   create index on items (color_id);
> 
> I thought this was the right way to do it, but now I’m not so sure... In
> application code, prepared statements want to say: `select * from items
> where color_id = ?` and that `?` might be a int or null, so that doesn’t
> work.  I used `is not distinct from` instead of =, which has the right
> meaning, but now I notice it doesn’t use the index for queries that replace
> `=` with `is not distinct from`, and queries run much slower.  Using
> `explain` confirms: it’s doing sequential scans where `=` was using index.

I test for NULL in my application code and emit '... WHERE foo = ?' if the 
value is not NULL and '... WHERE foo IS NOT NULL' otherwise. ISTR that that 
actually uses indexes.

> 
> So… is this bad DB design to use null to mean that an item has no color?
> Should I instead put a special row in `colors`, maybe with id = 0, to
> represent the “no color” value?  Or is there some way to make an index work
> with nulls and `is not distinct from`?
> 
> thank you,
> Rob



-- 
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] use null or 0 in foreign key column, to mean "no value"?