[Pgpool-general] Parallel Query mode, trying to make inserts

[Christopher Siwy]

David,

Your recommendation worked, however, if the pgpool service is stopped and
restarted the error returns and I have to go through the steps of rebuilding
a table and setting up the rules in the system db AGAIN.  This is a VERY
impractical task.

Why would this error return:
ERROR:  pgpool2 sql restriction
DETAIL:  cannot use SelectStmt in InsertStmt

I am also having the same issue, when I recreate a table and the partition
rules in the system db.  One would hope that the records would get
partitioned to the correct node based on the partition defined in the
distribution function.  This unfortunately does not happen and I am simply
getting replicate values in all nodes.  And as you witnessed, when I do a
select I don't get duplicates which is a good thing.  PgPool-II from my
experience does not do parallel query processing.  And this is what I have
hoped would work, but doesn't.  Hopefully, I don't have to turn to MySQL to
do parallel querying.

Chris

On Jan 6, 2008 5:41 PM, David Andersen <david at andersen.gs> wrote:

> Hi Christopher,
>
> I am totally new to Pgpool but I ran into the same problem myself. For me
> it arises if I stop and restart pgpool after the table I want to insert
> into, the distribution function and records in the system_db tables are all
> set up. To solve the problem I created a new table and a new distribution
> function and record in system_db.
>
> Something I think is unclear is whether pgpool is supposed to insert all
> records on all nodes or follow the distribution function and partition the
> records. With my setup all records are inserted on all nodes, though when
> selecting, i do not get duplicate records.
>
> Regards,
>
> David
>
> On Jan 6, 2008 10:22 PM, Christopher Siwy <topple at gmail.com> wrote:
>
> > Has anyone got this error?
> >
> > ERROR:  pgpool2 sql restriction
> > DETAIL:  cannot use SelectStmt in InsertStmt
> >
> > I have pgpool setup and running connecting to 3 backend servers.  I have
> > no problem running DDL queries such as creating tables and functions.  I
> > also have inserted the appropriate records in pgpool_catalog.dist_def and
> > pgpool_catalog.replicate_def tables on the system db.
> >
> > But when I run the command:
> > INSERT INTO test(test_id,test_val) VALUES (1,'be1');
> > I get the aforementioned error.
> >
> > My test table is setup as such:
> > CREATE TABLE test (test_id int, test_val character(10));
> >
> > My records in the system_db tables:
> > INSERT INTO pgpool_catalog.dist_def VALUES
> > ('test','public','test','test_id',ARRAY['test_id','test_val'],ARRAY['integer','character(10)'],'dist_def_test');
> >
> > INSERT INTO pgpool_catalog.replicate_def VALUES
> > ('test','public','test',ARRAY['test_id','test_val'],ARRAY['integer','character(10)']);
> >
> > My distribution function is as follows:
> > CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_test(val ANYELEMENT)
> > RETURNS INTEGER AS '
> > SELECT CASE WHEN $1 >= 0 and $1 < 10 THEN 0
> > WHEN $1 >= 10 and $1 < 20 THEN 1
> > WHEN $1 >= 20 and $1 < 30 THEN 2
> > END' LANGUAGE SQL;
> >
> > I also only have parallel_query_mode set in my pgpool.conf file
> >
> > Please help, I really would be interested in using pgpool-II for my
> > parallel query processing in PostgreSQL, but this is severely limiting my
> > change of using it.
> >
> >
> > _______________________________________________
> > Pgpool-general mailing list
> > Pgpool-general at pgfoundry.org
> > http://pgfoundry.org/mailman/listinfo/pgpool-general
> >
> >
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pgfoundry.org/pipermail/pgpool-general/attachments/20080106/1b981da1/attachment.html