Hi!
I have a database just recreated from scratch with SQL script.
(My PostgreSQL version is 8.1.3).
All of the tables are pretty empty, but that's what I've got with explain:
EXPLAIN SELECT * FROM account;
QUERY PLAN
Hi,
I think there has been some talk about this missing feature since 2001,
but anyway..
I have a database which contains about 500M rows and the nightly dump
takes about six hours. Size of the compressed dump is ~30GB.
The biggest tables are:
relname| reltuples
On Tue, Aug 15, 2006 at 10:14:49AM +0300, Andrii Vasyliev wrote:
> Hi!
>
> I have a database just recreated from scratch with SQL script.
> (My PostgreSQL version is 8.1.3).
> All of the tables are pretty empty, but that's what I've got with explain:
>
> EXPLAIN SELECT * FROM account;
>
Hi,
I just want to verify if I'm understanding this correctly:
I have a table in which I store log from my firewall.
For the protocol column (3 distinct values: TCP ~82%, UDP ~17%, ICMP
~1%, the table contains 1.7M rows), I use a partial index to find ICMP
packets faster.
In my understanding
On Sun, Aug 13, 2006 at 10:30:24AM +0200, Michael Meskes wrote:
> On Fri, Aug 11, 2006 at 04:40:36PM -0600, Michael Fuhr wrote:
> > Will you take care of it or should I submit a patch? I've noticed
>
> I you have the time to write the patch I woul dappreciate it.
I'll submit a patch. However, i
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> The database contains several schemas and excluding "comment_archive" by
> moving it to different schema doesn't sound very convenient. pg_dump
> doesn't have an option to dump multiple schemas at once.
>
> Are there any working "-X" patches for pg
On Tue, Aug 15, 2006 at 05:59:23AM -0600, Michael Fuhr wrote:
> I'll submit a patch. However, in the case of string literals not
> working, is that a documentation bug or a code bug? Are they
> supposed to work?
You shoudl be able to use a string constant or a char * variable as
database name. T
Greg Sabino Mullane wrote:
The database contains several schemas and excluding "comment_archive" by
moving it to different schema doesn't sound very convenient. pg_dump
doesn't have an option to dump multiple schemas at once.
Are there any working "-X" patches for pg_dump or does anyone have oth
Hi! Is it possible to use a function that returns SETOF and doesn't take constant parameters?I have a function 'connections(id1 bigint, id2 bigint) returns setof text' that I use to list all paths from id1 to id2. However, I haven't figured out a way to call the function with non-constant function
Hello all.
I'm working on a kind of permissions management and I would like to
add restrictions on SELECT statements depending on certain fields of
a row.
tablename :
id,
perm_field1,
..
..
perm_field2,
data_field1,
data_field2,
...
Do you know if it is possible to write such things :
SEL
On Tue, 2006-08-15 at 12:38 +0100, Pavel Velikhov wrote:
> SELECT t1.node, t2.node, path FROM t1,t2, connections(t1.id, t2.id) as
> path
>
> I get the notorious: ERROR: subquery in FROM may not refer to other
> relations of same query level
>
The FROM list must be a list of relations, but in t
Hi Micheal,sudde2nly a problem has cropped up in my connection.Its kind of strange.ECPGdegug(1, stderr) says[9852]: connect: cold not open database dbxyz on 192.168.0.123
port 5432 for user jsb in line 16 could not connect to server: No route to host Is the server running on host "192.168.
Hi:
I installed PostgreSQL 8.1.0 on my computer running on
Windows XP Service Pack 2 last April. It works fine (I
don't use the database everyday though). Today, after being
unable to connect to the server, I realized there must be
something wrong. Looking at the logs I found out that there
we
in my previous mail both the ip addressed should be read as 192.168.0.123thanksharpreetOn 8/15/06, Harpreet Dhaliwal <
[EMAIL PROTECTED]> wrote:Hi Micheal,sudde2nly a problem has cropped up in my connection.
Its kind of strange.ECPGdegug(1, stderr) says[9852]: connect: cold not open database db
On Tue, 2006-08-15 at 13:13 +0200, MaXX wrote:
> Hi,
>
> I just want to verify if I'm understanding this correctly:
>
> I have a table in which I store log from my firewall.
> For the protocol column (3 distinct values: TCP ~82%, UDP ~17%, ICMP
> ~1%, the table contains 1.7M rows), I use a parti
On Tue, 2006-08-15 at 18:05 +0200, Max wrote:
> Do you know if it is possible to write such things :
>
> SELECT * FROM tablename WHERE permission(some parameters) == TRUE;
>
> with the function 'permission' returning TRUE or FALSE after a check
> on different perm_fields of the current row.
Y
Ludwig Isaac Lim wrote on 15.08.2006 18:05:
Searching the web using google gives me the following
information about error 1063:
Error code 1063: ERROR_FAILED_SERVICE_CONTROLLER_CONNECT -
The service process could not connect to the service
controller. (from
http://user.tninet.se/~tdf275m/wincode2
Thx.
But I know how to write procedure and function, but my problem is to
know how to access the current row fields during a SELECT inside a
function:
So, in a function, can I write :
/* ... */ permission (/* ... */)
/* ... */
IF (ROW.perm_field1 = 1)
statement
IF (some_operation(RO
Its doneservice iptables stop did the trickfirewall was running on my DB serverforgot to stop itThanks~HarpreetOn 8/15/06, Michael Fuhr
<[EMAIL PROTECTED]> wrote:On Sun, Aug 13, 2006 at 10:30:24AM +0200, Michael Meskes wrote:
> On Fri, Aug 11, 2006 at 04:40:36PM -0600, Michael Fuhr wrote:> > Will
MaXX <[EMAIL PROTECTED]> writes:
> In my understanding, a partial index is only touched when a matching row is
> inserted/updated/deleted (index constraint is true), so if I create a partial
> index for each protocol, I will slow down my machine as if I had created a
> single "normal" index, but
Tom
thanks for to respond quickly, see below
> marcelo Cortez <[EMAIL PROTECTED]> writes:
> > Then i've created "normal" tables but the wal
> file
> > generated by this tables grows and grows.
>
> The WAL files shouldn't grow indefinitely unless
> you've got some
> fairly serious problem tha
Hi Thomas :
--- Thomas Kellerer <[EMAIL PROTECTED]> wrote:
> Ludwig Isaac Lim wrote on 15.08.2006 18:05:
> > Searching the web using google gives me the following
> > information about error 1063:
> > Error code 1063:
> ERROR_FAILED_SERVICE_CONTROLLER_CONNECT -
> I had a similar issue several we
On Tue, 2006-08-15 at 18:46 +0200, Max wrote:
> Thx.
> But I know how to write procedure and function, but my problem is to
> know how to access the current row fields during a SELECT inside a
> function:
>
> So, in a function, can I write :
>
> /* ... */ permission (/* ... */)
> /* ... */
>
I have a table that I would like to be
able to retrieve information out of based on a combination of multiple columns
and I would like to be able to do this through a plpgsql stored procedure.
Right now I have multiple stored procedures that I am calling based on the
values parameter values
Hello
I need many tables of type "id" and "name", see below:
CREATE TABLE id_names (
id smallint CONSTRAINT the_id PRIMARY KEY NOT
NULL,
name text CONSTRAINT the_name UNIQUE
) WITH OIDS;
therefore I created these tables with the "LIKE"
> I need many tables of type "id" and "name", see below:
> / CREATE TABLE id_names (
> idsmallintCONSTRAINT the_id PRIMARY KEY NOT NULL,
> nametextCONSTRAINT the_name UNIQUE
> ) WITH OIDS;/
> therefore I created these tables with the "LI
Richard Broersma Jr wrote:
I need many tables of type "id" and "name", see below:
/ CREATE TABLE id_names (
idsmallintCONSTRAINT the_id PRIMARY KEY NOT NULL,
nametextCONSTRAINT the_name UNIQUE
) WITH OIDS;/
therefore I created these tab
On Tue, 15 Aug 2006, gustavo halperin wrote:
> Hello
>
> I need many tables of type "id" and "name", see below:
> / CREATE TABLE id_names (
> idsmallintCONSTRAINT the_id PRIMARY KEY NOT NULL,
> nametextCONSTRAINT the_name UNIQUE
> ) WIT
> > it looks like the syntax here is a little off from what is defined by:
> > http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
> >
> > CREATE TABLE like_id_1 ( idsmallint like id_names,
> > name text like id_name ) with oids ;
> >
> Are you s
Jeff Davis <[EMAIL PROTECTED]> writes:
> What you want to do is pass each "perm_field" as a parameter.
I think the OP is hoping for some sort of magic action-at-a-distance
behavior whereby his function can access stuff that wasn't passed to it.
Doesn't exist though ...
reg
Gregory Stark <[EMAIL PROTECTED]> writes:
> But if you're just looking up a single record I wouldn't expect it to be much
> faster to look it up in the smaller partial index than in the larger index.
> Indexes find records in log(n) time and log() grows awfully slowly.
Yeah. Given the proportions
I have a trigger that updates a count table, based on status. The count
table looks like this:
key status count
a1 300
a2 400
b1 100
b2 200
The problem is that for large updates when I do "UPDATE table SET status
= 1 WHERE status = 2 and key =
Hello,
I have done a good deal of investigation and cannot seem to find a
straight answer. Is there way to change the default data directory?
I am using Ubuntu Dapper LTS. I have a seperate hard drive (and
partition) that I want to keep my data on in case of a problem with the
OS.
Any help wo
On Aug 15, 2006, at 1:19 PM, [EMAIL PROTECTED] wrote:
Hello,
I have done a good deal of investigation and cannot seem to find a
straight answer. Is there way to change the default data directory?
I am using Ubuntu Dapper LTS. I have a seperate hard drive (and
partition) that I want to keep
On 15 Aug 2006 11:19:39 -0700
[EMAIL PROTECTED] wrote:
> Hello,
>
> I have done a good deal of investigation and cannot seem to find a
> straight answer. Is there way to change the default data directory?
> I am using Ubuntu Dapper LTS. I have a seperate hard drive (and
> partition) that I wa
Hello
I have a table with an array of smallint that I want reference to a
column of smallint, see below:
/database=# CREATE TABLE id_names ( id smallint CONSTRAINT the_id
PRIMARY KEY NOT NULL,
database(# name text CONSTRAINT the_name UNIQUE ) WITH OIDS;
NOTICE: CREATE TABLE / PRIMAR
Curtis,
Here is an example function that uses dynamic sql.
I use it under 7.4.5
Hope this helps.
Allan
-- Function to delete old data out of the point tables.
-- tablename is a column in the points table that holds the name
-- of the table in which this points data is stored.
create or replace
Hi:
I have problem trying to add same days to a date.
I have this select :
SELECT '2006-08-01' + FP.carencia + ( FP.prazo * ( MFP.parcela - 1 )) AS
vencimento
FROM fi_mov_formas_pagamento MFP
LEFT OUTER JOIN fi_formas_pagamento FP ON ( MFP.idformapagamento =
FP.idformapagamento AND MFP
On Tue, Aug 15, 2006 at 10:10:27PM -0300, Alejandro Michelin Salomon ( Adinet )
wrote:
> EX :
> '2006-08-01' + 30 + ( 7 * ( 3 - 1 )) ==> '2006-08-01' + 44
>
> All my trys fails.
The error message hints at what's wrong:
test=> SELECT '2006-08-01' + 30 + (7 * (3 - 1));
ERROR: invalid input synta
On Tue, Aug 15, 2006 at 03:56:03PM +0200, Michael Meskes wrote:
> On Tue, Aug 15, 2006 at 05:59:23AM -0600, Michael Fuhr wrote:
> > I'll submit a patch. However, in the case of string literals not
> > working, is that a documentation bug or a code bug? Are they
> > supposed to work?
>
> You shou
Hi,
I'm using ECPG and trying to do follwoing insert in one of the talbes.
insert into raw_email ( id , raw_email ) values ( 1 , :ch);
ch is an array and defined as follows:
EXEC SQL BEGIN DECLARE SECTION; char ch[MSG_LEN];EXEC SQL END DECLARE SECTION;
contents of array ch is actually
On Tue, Aug 15, 2006 at 11:55:58PM -0400, Jasbinder Bali wrote:
> I'm using ECPG and trying to do follwoing insert in one of the talbes.
>
> insert into raw_email ( id , raw_email ) values ( 1 , :ch);
>
> ch is an array and defined as follows:
>
> EXEC SQL BEGIN DECLARE SECTION;
>char ch[M
table definition of raw_email table is as follows
CREATE TABLE raw_email ( idint4 NOT NULL, raw_email varchar[],
parsed_flag bool NOT NULL DEFAULT false,
CONSTRAINT pk_rawemail PRIMARY KEY (id))
WITHOUT OIDS;
i have very much declared raw_email field as varchar and n
hello everybody,
i want use a variable to denote a table name in select query.
for example:
DECLARE
table_message_data varchar;
record_message_data record;
BEGIN
table_message_data := prefix || 'message_data';
select into record_message_data * from table_message_data
On Wed, Aug 16, 2006 at 12:38:42AM -0400, Jasbinder Bali wrote:
> table definition of raw_email table is as follows
>
> CREATE TABLE raw_email (
> idint4 NOT NULL,
> raw_email varchar[],
> parsed_flag bool NOT NULL DEFAULT false,
> CONSTRAINT pk_rawemail PRIMARY KEY (id)
On Wed, Aug 16, 2006 at 12:43:07PM +0800, aBBISh wrote:
> i want use a variable to denote a table name in select query.
See "Executing Dynamic Commands" in the PL/pgSQL documentation:
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
--
Mich
This is how the array is formed in my C code
---
FILE *fp;
while(!feof(fp)) {ch[i]=fgetc(fp); if(ch[i]=='\n') lines++; i++; } ch[i-1]='\0'; fclose(fp);---
Hi,I'm trying to create a trigger with the following definition:CREATE TRIGGER insert_price_change AFTER INSERT OR DELETE OR UPDATE ON raw_email FOR EACH ROW EXECUTE PROCEDURE add_one(1);Procedure / Function add_one has the following definition
CREATE FUNCTION add_one(integer) RETURNS integer
On Wed, Aug 16, 2006 at 01:20:08AM -0400, Jasbinder Bali wrote:
> This is how the array is formed in my C code
> ---
> FILE *fp;
>
> while(!feof(fp))
> {ch[i]=fgetc(fp);
> if(ch[i]=='\n') lines++;
I changed the datatype from varchar[] to varchar
ECPGdebug(1,stderr) says
[2998]: ECPGexecute line 97 Ok: INSERT 0 1
Its not inserting any record even though sqlcode is 0.
~Jas
On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 01:20:08AM -0400, Jasbinder Bali wrote:>
Also,
My insert statement is
EXEC SQL INSERT INTO raw_email (id,raw_email) VALUES (1,:ch);
On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 01:20:08AM -0400, Jasbinder Bali wrote:> This is how the array is formed in my C code
> --
On Wed, Aug 16, 2006 at 01:35:47AM -0400, Harpreet Dhaliwal wrote:
> I'm trying to create a trigger with the following definition:
>
> CREATE TRIGGER insert_price_change AFTER INSERT OR DELETE OR UPDATE ON
> raw_email
> FOR EACH ROW EXECUTE PROCEDURE add_one(1);
>
> Procedure / Function add_one
On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote:
> I changed the datatype from varchar[] to varchar
> ECPGdebug(1,stderr) says
> [2998]: ECPGexecute line 97 Ok: INSERT 0 1
>
> Its not inserting any record even though sqlcode is 0.
Are you committing the transaction? See the bottom
I got your point.
however, my requirement is something like this.
The trigger shold start another function (a stored procedure) after any event is fired.
how do I accomplish this goal?
Harpreet
On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 01:35:47AM -0400, Harpr
Its actually something like the trigger should start a C function after insert and the C function has the ECPG code for some more inserts.
Its similar to the way we dynamically load a shared library while executing a stored procedure, as in , executing a fucntion in C file using stored procedure/ f
Its actually something like the trigger should start a C function after insert and the C function has the ECPG code for some more inserts.
Its similar to the way we dynamically load a shared library while executing a stored procedure, as in , executing a fucntion in C file using stored procedure/
56 matches
Mail list logo
