[SQL] problem with rules

[Uwe Bartels]

Hi,

i'm trying to reorganize a big table without having a downtime at all. I'm
trying to realize this via partitioning and rules.

- step 1 preparation
create table uglybloatedtable (id bigserial, textfield text, primary
key(id));
INSERT INTO uglybloatedtable (textfield) values ('');
INSERT INTO uglybloatedtable (textfield) values ('');
INSERT INTO uglybloatedtable (textfield) values ('');
INSERT INTO uglybloatedtable (textfield) values ('');
INSERT INTO uglybloatedtable (textfield) values ('');
INSERT INTO uglybloatedtable (textfield) values ('');

- step 2 transform table into a partitioned table with an additional
partition key
create table nicenonbloatedtable1 (id bigint not null default
nextval('uglybloatedtable_id_seq'::regclass), textfield text,
partitioningkey char(1) default '1') inherits (uglybloatedtable);

alter table nicenonbloatedtable1 add partitioningkey char(1) default '1';
alter table add constraint pcheck_uglybloatedtable1 CHECK
(partitioningkey=1);
alter table nicenonbloatedtable1 add constraint pcheck_uglybloatedtable1
CHECK (partitioningkey='1');

- step 3 redirect inserts via rule
CREATE RULE nicenonbloatedtable1_insert AS
ON INSERT TO uglybloatedtable
DO INSTEAD
INSERT INTO nicenonbloatedtable1 (id,textfield) VALUES (NEW.*);

- step 4 handle updates - this does not work yet.
CREATE or REPLACE RULE nicenonbloatedtable1_update AS
ON UPDATE TO uglybloatedtable
DO INSTEAD (
DELETE FROM uglybloatedtable where id=OLD.id;
DELETE FROM nicenonbloatedtable1 where id=OLD.id;
INSERT INTO nicenonbloatedtable1 (id,textfield) VALUES (NEW.*);
);

running an insert, redirects the insert into the partition - fine.
running an update like
update uglybloatedtable set id=2, textfield='migrated' where id=8;
removes the row in uglybloatedtable, but does not insert the row into the
table nicenonbloatedtable1.

So now I have 2 questions:
- what's wrong?
- how can i debug these rules? is there a way to see the executed
statements? explain plan did not help, set client_min_messages=debug5
neither.

best regards,
Uwe

[SQL] plpgsql exception handling

[Uwe Bartels]

Hi,

I'm trying to run an execute with a dynamic sql command within a function.
I need a clean exception handling here, but my version does not work
somehow.

I want to trap a possible syntax error and write the error code, error
message and the sql into a table.

the function looks similar to this, I cut out the unimportant pieces.

CREATE OR REPLACE FUNCTION report_process(p_sql text)
  RETURNS integer AS
$BODY$
DECLARE
l_state smallint;
l_message text;
BEGIN

l_state=0;
begin
execute 'create table result_'||p_id||' as '||p_sql;
exception when others then
l_state=-3;
l_message:='Error executing sql sql error code: %, sql error
message: %, sql: %',SQLSTATE,SQLERRM,l_sql;
end;

update "cache"
set c_date=now(), c_state=l_state, c_message=l_message
where c_id=p_id;

return 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;


This is the error message when I call the function
select report_process('select 1count(*) from event_log_day'::text);


ERROR:  syntax error at or near "("
LINE 1: ...e table result_9 as select 1count(*) from d...
 ^
QUERY:  create table result_9 as select 1count(*) from event_log_day
CONTEXT:  PL/pgSQL function "report_process" line 31 at EXECUTE statement

** Error **

ERROR: syntax error at or near "("
SQL state: 42601
Context: PL/pgSQL function "report_process" line 31 at EXECUTE statement

Any help is appreciated.

best regards,
Uwe

Re: [SQL] plpgsql exception handling

[Uwe Bartels]

Hi,
Yes, of course is this sql producing an error.
The point is, I want to trap the error and handle it. Here in this case I
set the variable l_state and l_message.

But
The function exits with an exception instead of returning. So the exception
statement does not work as I think i would.

And I don't know why.

Best...
Uwe
PS: p_id is a variable in my code which is bigger. so ignore the update
statement.


On 9 March 2011 23:08, Samuel Gendler  wrote:

> when I run 'select 1count(*) from table' in my postgres 8.4 installation, I
> get the exact same error message.  Assuming the '1count()' function does
> exist, perhaps you need to full qualify it with a schema name?
>
> It looks to me like the query you are passing to the procedure is invalid
> and is generating the error.  Perhaps the 1 in front of count(*) is a typo?
>
>
> On Wed, Mar 9, 2011 at 1:55 PM, Uwe Bartels  wrote:
>
>> Hi,
>>
>> I'm trying to run an execute with a dynamic sql command within a function.
>> I need a clean exception handling here, but my version does not work
>> somehow.
>>
>> I want to trap a possible syntax error and write the error code, error
>> message and the sql into a table.
>>
>> the function looks similar to this, I cut out the unimportant pieces.
>>
>> CREATE OR REPLACE FUNCTION report_process(p_sql text)
>>   RETURNS integer AS
>> $BODY$
>> DECLARE
>> l_state smallint;
>> l_message text;
>> BEGIN
>>
>> l_state=0;
>> begin
>> execute 'create table result_'||p_id||' as '||p_sql;
>> exception when others then
>> l_state=-3;
>> l_message:='Error executing sql sql error code: %, sql error
>> message: %, sql: %',SQLSTATE,SQLERRM,l_sql;
>> end;
>>
>> update "cache"
>> set c_date=now(), c_state=l_state, c_message=l_message
>> where c_id=p_id;
>>
>> return 0;
>> END;
>> $BODY$
>>   LANGUAGE plpgsql VOLATILE SECURITY DEFINER
>>   COST 100;
>>
>>
>> This is the error message when I call the function
>> select report_process('select 1count(*) from event_log_day'::text);
>>
>>
>> ERROR:  syntax error at or near "("
>> LINE 1: ...e table result_9 as select 1count(*) from d...
>>  ^
>> QUERY:  create table result_9 as select 1count(*) from event_log_day
>> CONTEXT:  PL/pgSQL function "report_process" line 31 at EXECUTE statement
>>
>> ** Error **
>>
>> ERROR: syntax error at or near "("
>> SQL state: 42601
>> Context: PL/pgSQL function "report_process" line 31 at EXECUTE statement
>>
>> Any help is appreciated.
>>
>> best regards,
>> Uwe
>>
>
>

Re: [SQL] plpgsql exception handling

[Uwe Bartels]

yes, p_id is a variable in my code which is bigger. so ignore the update
statement.

Uwe

On 10 March 2011 01:20, bricklen  wrote:

> On Wed, Mar 9, 2011 at 2:08 PM, Samuel Gendler
>  wrote:
> > when I run 'select 1count(*) from table' in my postgres 8.4 installation,
> I
> > get the exact same error message.  Assuming the '1count()' function does
> > exist, perhaps you need to full qualify it with a schema name?
> > It looks to me like the query you are passing to the procedure is invalid
> > and is generating the error.  Perhaps the 1 in front of count(*) is a
> typo?
>
> Also seem to be missing "p_id" from your execute statement:
>
> execute 'create table result_'|| p_id ||' as '||p_sql;
>

Re: [SQL] plpgsql exception handling

[Uwe Bartels]

same same.
all errors including syntax_error match to others, but I checked it again.
and the exception remains.

Uwe



On 10 March 2011 10:56, Samuel Gendler  wrote:

>
>
> On Thu, Mar 10, 2011 at 12:45 AM, Uwe Bartels wrote:
>
>> Hi,
>>
>> Yes, of course is this sql producing an error.
>> The point is, I want to trap the error and handle it. Here in this case I
>> set the variable l_state and l_message.
>>
>
>
> Doh!  Of course.  Sorry about that.  What happens when you explicitly catch
> the 42601 sql state or the syntax_error condition instead of 'others?'
>
>
>

Re: [SQL] plpgsql exception handling

[Uwe Bartels]

stupid me.
I edited a function with the same name, but different parameter types and
tested with the other function.
so everything works fine.

thanks everybody for help.

best regards,
Uwe


On 10 March 2011 11:53, Uwe Bartels  wrote:

> same same.
> all errors including syntax_error match to others, but I checked it again.
> and the exception remains.
>
> Uwe
>
>
>
>
> On 10 March 2011 10:56, Samuel Gendler  wrote:
>
>>
>>
>> On Thu, Mar 10, 2011 at 12:45 AM, Uwe Bartels wrote:
>>
>>> Hi,
>>>
>>> Yes, of course is this sql producing an error.
>>> The point is, I want to trap the error and handle it. Here in this case I
>>> set the variable l_state and l_message.
>>>
>>
>>
>> Doh!  Of course.  Sorry about that.  What happens when you explicitly
>> catch the 42601 sql state or the syntax_error condition instead of 'others?'
>>
>>
>>
>

[SQL] using explain output within pgsql

[Uwe Bartels]

Hi,

I'm starting up a datawarehouse with patitioning.
my etl processes write directly into the corresponding partitions instead of
using triggers.

The reports I run in the datawarehouse are stored in a cache within the same
database.
Now I'd like to store besides the results the dependencies to the tables
which were used to generate the report. with this information i could
invalidate cache results for the tables I'm going to import with my etl
processes.

explain analyze gives me the information which table or patition is read
from for each report. e.g
explain analyze (FORMAT YAML) create table cache.report234 as select
col1,col2,sum(clicks) from dwh.event_log_weekly where week >= '2011-06-27'
and week <= '2011-07-11' group by col1,col2;

now I'd like to store the output of explain analyze in a pgsql variable for
further processing. that looks something like this.

DO $$declare l_explain text;
begin
l_explain := explain analyze (FORMAT YAML) create table cache.report234 as
select col1,col2,sum(clicks) from dwh.event_log_weekly where week >=
'2011-06-27' and week <= '2011-07-11' group by col1,col2;
select l_explain;
end$$;

But that doesn't work. I get a syntax error.

Does anybody has an idea how to retrieve the output of explain within pgsql
and store this in a variable?
An alternative would be any other way to extract the information about
tables used by arbitrary sql statements.

best regards,
Uwe

Re: [SQL] using explain output within pgsql

[Uwe Bartels]

Hi Adrian,

yes. but this is not the cause for the syntax error.

thanks,
Uwe




On 10 July 2011 21:20, Adrian Klaver  wrote:

> On Sunday, July 10, 2011 11:54:10 am Uwe Bartels wrote:
> > Hi,
> >
> > I'm starting up a datawarehouse with patitioning.
> > my etl processes write directly into the corresponding partitions instead
> > of using triggers.
> >
> > The reports I run in the datawarehouse are stored in a cache within the
> > same database.
> > Now I'd like to store besides the results the dependencies to the tables
> > which were used to generate the report. with this information i could
> > invalidate cache results for the tables I'm going to import with my etl
> > processes.
> >
> > explain analyze gives me the information which table or patition is read
> > from for each report. e.g
> > explain analyze (FORMAT YAML) create table cache.report234 as select
> > col1,col2,sum(clicks) from dwh.event_log_weekly where week >=
> '2011-06-27'
> > and week <= '2011-07-11' group by col1,col2;
> >
> > now I'd like to store the output of explain analyze in a pgsql variable
> for
> > further processing. that looks something like this.
> >
> > DO $$declare l_explain text;
> > begin
> > l_explain := explain analyze (FORMAT YAML) create table cache.report234
> as
> > select col1,col2,sum(clicks) from dwh.event_log_weekly where week >=
> > '2011-06-27' and week <= '2011-07-11' group by col1,col2;
> > select l_explain;
> > end$$;
> >
> > But that doesn't work. I get a syntax error.
>
> From here:
> http://www.postgresql.org/docs/9.0/interactive/sql-explain.html
>
> I believe you are looking for:
> explain (ANALYZE, FORMAT YAML) create table...
>
> >
> > Does anybody has an idea how to retrieve the output of explain within
> pgsql
> > and store this in a variable?
> > An alternative would be any other way to extract the information about
> > tables used by arbitrary sql statements.
> >
> > best regards,
> > Uwe
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>

Re: [SQL] using explain output within pgsql

[Uwe Bartels]

Hi Pavel,

I don't believe it. the second answer on sunday evening within half an hour.

and it works. excellent!!!

best regards,
Uwe


On 10 July 2011 21:20, Pavel Stehule  wrote:

> Hello
>
>
>
> 2011/7/10 Uwe Bartels :
> > Hi,
> >
> > I'm starting up a datawarehouse with patitioning.
> > my etl processes write directly into the corresponding partitions instead
> of
> > using triggers.
> >
> > The reports I run in the datawarehouse are stored in a cache within the
> same
> > database.
> > Now I'd like to store besides the results the dependencies to the tables
> > which were used to generate the report. with this information i could
> > invalidate cache results for the tables I'm going to import with my etl
> Hello
>
> try
>
> FOR l_explain IN EXPLAIN ANALYZE ...
> LOOP
>   ...
>
> Regards
>
> Pavel Stehule
>
> > processes.
> >
> > explain analyze gives me the information which table or patition is read
> > from for each report. e.g
> > explain analyze (FORMAT YAML) create table cache.report234 as select
> > col1,col2,sum(clicks) from dwh.event_log_weekly where week >=
> '2011-06-27'
> > and week <= '2011-07-11' group by col1,col2;
> >
> > now I'd like to store the output of explain analyze in a pgsql variable
> for
> > further processing. that looks something like this.
> >
> > DO $$declare l_explain text;
> > begin
> > l_explain := explain analyze (FORMAT YAML) create table cache.report234
> as
> > select col1,col2,sum(clicks) from dwh.event_log_weekly where week >=
> > '2011-06-27' and week <= '2011-07-11' group by col1,col2;
> > select l_explain;
> > end$$;
> >
> > But that doesn't work. I get a syntax error.
> >
> > Does anybody has an idea how to retrieve the output of explain within
> pgsql
> > and store this in a variable?
> > An alternative would be any other way to extract the information about
> > tables used by arbitrary sql statements.
> >
> > best regards,
> > Uwe
> >
>

Re: [SQL] using explain output within pgsql

[Uwe Bartels]

Hi Pavel,

is it posible to get this running even with dynamic sql?
I didn't write that. I'm using execute to run this create table 

best regards,
Uwe

On 10 July 2011 21:20, Pavel Stehule  wrote:

> Hello
>
>
>
> 2011/7/10 Uwe Bartels :
> > Hi,
> >
> > I'm starting up a datawarehouse with patitioning.
> > my etl processes write directly into the corresponding partitions instead
> of
> > using triggers.
> >
> > The reports I run in the datawarehouse are stored in a cache within the
> same
> > database.
> > Now I'd like to store besides the results the dependencies to the tables
> > which were used to generate the report. with this information i could
> > invalidate cache results for the tables I'm going to import with my etl
> Hello
>
> try
>
> FOR l_explain IN EXPLAIN ANALYZE ...
> LOOP
>   ...
>
> Regards
>
> Pavel Stehule
>
> > processes.
> >
> > explain analyze gives me the information which table or patition is read
> > from for each report. e.g
> > explain analyze (FORMAT YAML) create table cache.report234 as select
> > col1,col2,sum(clicks) from dwh.event_log_weekly where week >=
> '2011-06-27'
> > and week <= '2011-07-11' group by col1,col2;
> >
> > now I'd like to store the output of explain analyze in a pgsql variable
> for
> > further processing. that looks something like this.
> >
> > DO $$declare l_explain text;
> > begin
> > l_explain := explain analyze (FORMAT YAML) create table cache.report234
> as
> > select col1,col2,sum(clicks) from dwh.event_log_weekly where week >=
> > '2011-06-27' and week <= '2011-07-11' group by col1,col2;
> > select l_explain;
> > end$$;
> >
> > But that doesn't work. I get a syntax error.
> >
> > Does anybody has an idea how to retrieve the output of explain within
> pgsql
> > and store this in a variable?
> > An alternative would be any other way to extract the information about
> > tables used by arbitrary sql statements.
> >
> > best regards,
> > Uwe
> >
>

[SQL] strange error message

[Uwe Bartels]

Hi,

I've got a strange error message in our java application.
the only reference to this message i find in
http://mingo.info-science.uiowa.edu/eichmann/courses/database/Postgres/programmer.pdf.
but I still don't know how to solve this or even what the source od this
error is.
currently I don't have the executed sql yet. I'm trying to get this.
Any Idea what that could be?

ERROR JDBCTransaction:124 - JDBC commit failed
*org.postgresql.util.PSQLException: ERROR: bogus varno: 65001*
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at
org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:671)
at
org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:691)
at sun.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$ConnectionHandler.invoke(AbstractJdbc23PooledConnection.java:352)
at $Proxy24.commit(Unknown Source)
at
org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:139)
at
org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:115)
...

Thanks,
Uwe

Re: [SQL] strange error message

[Uwe Bartels]

 Hi Tom,

Cool. Thanks for your answer.

Best Regards,
Uwe

On 25 November 2011 17:43, Tom Lane  wrote:

> Uwe Bartels  writes:
> > I've got a strange error message in our java application.
>
> > ERROR JDBCTransaction:124 - JDBC commit failed
> > *org.postgresql.util.PSQLException: ERROR: bogus varno: 65001*
>
> This is a symptom of some kind of planner error.  If you are not
> running the latest minor release of your Postgres branch, update
> and see if it goes away.  If not, please file a bug report with
> sufficient information to reproduce the problem by hand (ie, the
> problem query plus schema+data sufficient to run it against).
>
>regards, tom lane
>