Обсуждение: Fwd: [pgsql-es-ayuda] Ejecutar \copy desde VB

---------- Forwarded message ----------
From: Carlos Alberto Silva <casxxi@yahoo.com.ar>
Date: Jun 19, 2007 2:00 PM
Subject: Re: [pgsql-es-ayuda] Ejecutar \copy desde VB
To: Jaime Casanova <systemguards@gmail.com>


Yo lo haría de otra manera. Una rutina q lea el archivo de texto en VB
(nada complicado de hacer) y luego los inserts correspondientes en la
tabla via ODBC o como sea q me conecte a la base.


IMHO.Carlos

Jaime Casanova escribió:
> On 6/19/07, Gabriel Hermes Colina Zambra <hermeszambra@yahoo.com> wrote:
>> >
>> Otra burda manera pero menos complicada, es crear un
>> .bat que maneje set pgpassword=clavedeusuariopostgres
>
> en vez de setear pgpassword deberias usar un archivo pgpass.conf
> http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html
>
>> y ejecutar el bat con la instruccion shell desde
>> visual basic, quiza teniendo un lnk apuntando al bat.
>> Es una idea burda pero funciona.
>>
>
> pero creo, no estoy seguro porque Chapiliquen no lo dejo claro, que
> quiere hacer eso desde un cliente y no desde el servidor... y dudo
> mucho que quiera tener recursos compartidos en cada cliente solo para
> eso... ahora que si esta dispuesto esa seria una solucion (claro que,
> segun yo, necesitaria ejecutar el bat en el servidor)
>
>
>



--
Atentamente,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."                                      Richard Cook

It seems constraint exclusion is not working with my partitioned tables and
the rules I wrote. 
This is my partition deployment:

-- Master table
CREATE TABLE table_master (setuptime timestamp with time zone NOT NULL,...
};

-- Partitions
CREATE TABLE table_p01 INHERITS (table_master);
CREATE TABLE table_p02 INHERITS (table_master);
...
CREATE TABLE table_p12 INHERITS (table_master);

-- Constraints: one partition per month
ALTER TABLE table_p01 ADD CONSTRAINT chk_table_p01_setuptime CHECK
(EXTRACT(MONTH FROM setuptime) =  1::DOUBLE PRECISION);
ALTER TABLE table_p02 ADD CONSTRAINT chk_table_p02_setuptime CHECK
(EXTRACT(MONTH FROM setuptime) =  2::DOUBLE PRECISION);
...
ALTER TABLE table_p12 ADD CONSTRAINT chk_table_p12_setuptime CHECK
(EXTRACT(MONTH FROM setuptime) = 12::DOUBLE PRECISION);


-- Insert rules for each partition table
CREATE OR REPLACE RULE rule_master_insert_01 AS ON INSERT TO table_master
WHERE (EXTRACT(MONTH FROM setuptime) = 1::double precision)
DO INSTEAD INSERT INTO table_p01 VALUES (NEW.setuptime
}
...

-- Index on setuptime for each partition table
CREATE INDEX idx_table_01_setuptime ON table_p01 USING btree (setuptime);
...

-- Constraint exclusion
> show constraint_exclusion
on

-- Execution plan
Explain select * from table_master where setuptime between
'2007.04.01'::timestamptz and '2007.06.01'::timestamptz

QUERY PLAN                                  
----------------------------------------------------------------------------Result  (cost=0.00..102699.64 rows=2333387
width=528) ->  Append  (cost=0.00..102699.64 rows=2333387 width=528)        ->  Seq Scan on table  (cost=0.00..1194.28
rows=12015width=320)              Filter: ((setuptime >= '2007-04-01 00:00:00-03'::timestamp
 
with time zone) AND (setuptime <= '2007-06-01 00:00:00-03'::timestamp with
time zone))        ->  Index Scan using idx_table_p01_setuptime on table_p01 table
(cost=0.00..3.03 rows=1 width=285)              Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))        ->  Index Scan using idx_table_p02_setuptime on table_p02 table
(cost=0.00..3.02 rows=1 width=286)              Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))        ->  Index Scan using idx_table_p03_setuptime on table_p03 table
(cost=0.00..5.95 rows=1 width=233)              Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))        ->  Seq Scan on table_p04 table  (cost=0.00..50117.83 rows=1139895
width=232)              Filter: ((setuptime >= '2007-04-01 00:00:00-03'::timestamp
with time zone) AND (setuptime <= '2007-06-01 00:00:00-03'::timestamp with
time zone))        ->  Seq Scan on table_p05 table  (cost=0.00..51343.54 rows=1181467
width=231)              Filter: ((setuptime >= '2007-04-01 00:00:00-03'::timestamp
with time zone) AND (setuptime <= '2007-06-01 00:00:00-03'::timestamp with
time zone))        ->  Index Scan using idx_table_p07_setuptime on table_p07 table
(cost=0.00..4.83 rows=1 width=528)              Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))        ->  Index Scan using idx_table_p08_setuptime on table_p08 table
(cost=0.00..4.83 rows=1 width=528)              Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))        ->  Index Scan using idx_table_p09_setuptime on table_p09 table
(cost=0.00..4.83 rows=1 width=528)              Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))        ->  Index Scan using idx_table_p10_setuptime on table_p10 table
(cost=0.00..4.83 rows=1 width=528)              Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))        ->  Index Scan using idx_table_p11_setuptime on table_p11 table
(cost=0.00..4.83 rows=1 width=528)              Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))        ->  Index Scan using idx_table_p12_setuptime on table_p12 table
(cost=0.00..4.83 rows=1 width=528)              Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))        ->  Index Scan using idx_table_p06_setuptime on table_p06 table
(cost=0.00..3.04 rows=1 width=273)              Index Cond: ((setuptime >= '2007-04-01
00:00:00-03'::timestamp with time zone) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
(28 rows)


The plan shows that it scans the indexes for all partitions when it should
only scan indexes for partitions 4 and 5. Is my assumption correct? If it
is, could someone point me out what I am doing wrong? I can't figure out why
it doesn't work.
I think the caveats mentioned in the manual about constraint exclusion have
been taken into account here but I might have missed something. 

Regards,
Fernando.

"Fernando Hevia" <fhevia@ip-tel.com.ar> writes:
> -- Constraints: one partition per month
> ALTER TABLE table_p01 ADD CONSTRAINT chk_table_p01_setuptime CHECK
> (EXTRACT(MONTH FROM setuptime) =  1::DOUBLE PRECISION);

The planner is not able to do anything with these constraints, other
than if there is an exact match to them in the query WHERE, which
there is not.  Try simple range constraints on the column, instead.
The system does know about inferences like "colx <= const1 must
imply colx <= const2 if const1 <= const2".  It does not know how
to reason about extract().
        regards, tom lane

I see. Thanks for the tip.

Regards,
Fernando.

-----Mensaje original-----
De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Enviado el: Miércoles, 20 de Junio de 2007 19:37
Para: Fernando Hevia
CC: 'PostgreSQL SQL List'
Asunto: Re: [SQL] Constraint exclusion

"Fernando Hevia" <fhevia@ip-tel.com.ar> writes:
> -- Constraints: one partition per month
> ALTER TABLE table_p01 ADD CONSTRAINT chk_table_p01_setuptime CHECK
> (EXTRACT(MONTH FROM setuptime) =  1::DOUBLE PRECISION);

The planner is not able to do anything with these constraints, other
than if there is an exact match to them in the query WHERE, which
there is not.  Try simple range constraints on the column, instead.
The system does know about inferences like "colx <= const1 must
imply colx <= const2 if const1 <= const2".  It does not know how
to reason about extract().
        regards, tom lane