On Fri, Jun 26, 2015 at 10:59 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
On Friday, June 26, 2015, Robert Nikander wrote:
>
> 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 n
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:
>
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
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 o
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 an
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
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 i
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 format
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.postgresq
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 plann
娄帅 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.
>
>
>
>
> 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
> c
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_
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:
>
> 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
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-spe
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, Postgr
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_sessionTimeou
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
> 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_s
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_sessionTi
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
=?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
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
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 = 32
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 ad
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
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
>
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
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
RA
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 t
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
> 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.
--
Se
> 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 fo
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_forced
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 st
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_cle
38 matches
Mail list logo
