Friday, 26 January 2007
|
| Using Temporary Tables in postgres functions Mario Splivalo 17:57:32 |
| | When I try to use TEMPORARY TABLE within postgres functions (using 'sql'
as a function language), I can't because postgres can't find that
temporary table. Consider this example:
CREATE FUNCTION func1() RETURNS SETOF v_messages_full AS $BODY$
CREATE TEMPORARY TABLE tmpTbl
AS
SELECT
message_id
FROM
cached_messages
WHERE
billing_status = 2;
UPDATE cached_messages SET billing_status = 1 WHERE message_id IN
(SELECT message_id FROM tmpTbl);
SELECT
*
FROM
v_messages_full
WHERE
message_id IN (SELECT message_id FROM tmpTbl);
$BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
When I try to execute above CREATE FUNCTION statement postgres gives me
this:
ERROR: relation "tmptbl" does not exist
CONTEXT: SQL function "func1"
If I rewrite func1() using 'plpgsq' I have no troubles creating
temporary tables, I just need to use EXEC when referencing to those
temporary tables (which is cumbersome, but there it is).
Am I doing something wrong here, or there is no way of using temporary
tables within 'sql' written functions?
Mike
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| |  7 answers | Add comment |
|
| LEFT Join Question Rob V 17:44:43 |
| | Hello all,
Ive been racking my brain for a few hours now and need some help, please!!!
I have the following tables :
account
=account_id
=account_type_id
=account_username
vendor
=account_id
=vendor_status
contact_name
=account_id
=name
=domain_type_id
contact_address
=account_id
=address
=domain_type_id
contact_email
=account_id
=email
=domain_type_id
contact_phone
=account_id
=phone
=domain_type_id
account_type
=account_type_id
= account_type_tag
records :
1 VENDOR
2 SELLER
3 CONTRACTOR
domain_type
=domain_type_id
=domain_type_tag
records :
1 PRIMARY
2 SECONDARY
Im looking for a select that will give me all records where the
account_type_tag ="VENDOR" and domain_type_tag = "PRIMARY"
even if the address, email and phone are blank.
Users WILL HAVE records in the following tables :
account,
vendor
contact_name
contact_email
they MAY or MAYNOT have records in the following tables :
contact_address
contact_phone
I know I have to use a left join - but I can seem to figure out the syntax
when dealing w/ different columns of the same table.
Im basically looking for this :
account_username vendor_status name address
email phone
---------------------------------------------------------------------------------------------------------------------------------------------------------
Rob123 ACTIVE ROB 123
Test Drive rob@here.com 555-1212
BILL123 ACTIVE Bill
NULL bill@here.com 456-4444
Steve1234 INACTIVE Steve 654
Hill St steve@here.com NULL
I know I have to use a left join - but I can seem to figure out the syntax
when dealing w/ different columns of the same table.
(do I still need the = in the where clause when using a left join?)
SELECT
account.account_id,
account.account_username,
vendor.vendor_status,
contact_name.name,
contact_address.address,
contact_email.email,
contact_phone.phone
FROM
account a
LEFT JOIN contact_phone on (contact_phone.account_id = a.account_id)
LEFT JOIN contact_address on (contact_address.account_id = a.account_id),
vendor,
contact_email,
contact_name,
domain_type,
account_type,
WHERE
vendor.vendor_id = account.account_id AND
contact_email.account_id = account.account_id AND
contact_name.account_id = account.account_id AND
account.account_type_id = account_type.account_type_id AND
contact_email.domain_type_id = domain_type.domain_type_id AND
contact_name.domain_type_id = domain_type.domain_type_id AND
vendor.vendor_status_code_id = vendor_status_code.vendor_status_code_id AND
account_type.account_type_tag = 'ACCOUNT_VENDOR' AND
domain_type.domain_type_tag = 'VENDOR_PRIMARY'
The problem Im having is b/c Im only looking for specific domain types I
have a join on the contact address and phone tables where the
domain types match - but if there are no records - it causes the entire
record not to be show - so I need to do a left join on that table as well
but If I try to do it - I get an error " table name "contact_phone"
specified more than once"
Any help would be great!!!
Thanks
-Rob
|
| | 4 answer | Add comment |
|
| Evaluate a variable Lu s Sousa 17:19:11 |
| | Hi,
Is there anyway to evaluate a variable in plpgsql, like eval on PHP?
Suppose the example:
my_var:=''some value!'';
a:=''my_var'';
b:=<eval!!> a;
I already tried b:=EXECUTE a; without luck!
Best regards,
Lu s Sousa
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 1 answer | Add comment |
|
| large document multiple regex Merlin Moncure 17:06:50 |
| | Hello,
I am receiving a large (300k+_ document from an external agent and
need to reduce a few interesting bits of data out of the document on
an insert trigger into separate fields.
regex seems one way to handle this but is there any way to avoid
rescanning the document for each regex. One solution I am kicking
around is some C hackery but then I lose the expressive power of
regex. Ideally, I need to be able to scan some text and return a
comma delimited string of values extracted from it. Does anybody know
if this is possible or have any other suggestions?
merlin
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | Add comment |
|
| trigger question. Alain Bruneau 16:31:48 |
| | Hi.
Has anyone migrated from Oracle to Postgresql there ?
I have a problem with trigger. I don't find a way to disable it.
Oracle statement :
ALTER TRIGGER "TRG_DT_ACTI" ENABLE
or
ALTER TRIGGER "TRG_DT_ACTI" DISABLE
I am looking for a way to do the same thing with postgresql.
I want to disable trigger before COPY and enable it after.
How can I do that?
Thank you for your help.
Regards,
Alain.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
| | 30 answers | Add comment |
|
| [pgsql-patches] uuid patch 3.0 (8.3devel) Gevik Babakhani 15:11:56 |
| | Folks,
As commented by Peter, I have done some re-styling.
Some additional tests and format checking have been added to this patch.
Put your file at the end of the OBJS variable (or in some sort of > sensible order). Done.
Put your file at the end of the tests (or in some sort of sensible > order).
Done.
Refrain from self-evident comments, such as> + /*> + * function handles input for the uuid datatype> + */> + Datum uuid_in(PG_FUNCTION_ARGS)> You can probably delete all comments in your patch by that criterion.
Some changed, but not all has been removed for readability reasons.
This sort of super-verbose coding might be alright, but it gets tiring > when done systematically for no reason:> + result = DirectFunctionCall1(textin, uuid_str);> + return result;
Some changed, but not all.
The uuid.c file claims it is uuid.h.
Changed.
Move the stuff from builtins.h to uuid.h. Not changed. please see:
http://archives.postgresql.org/pgsql-patches/2007-01/msg00362.php
Move the stuff from uuid.h that is not needed anywhere else to uuid.c.
Done.
No // comments.
Done.
Don't number the tests. We might want to insert something later and > that would mess everything up.
Done.
Capitalize the SQL test scripts as in other files.
Done.
Remove gratuitous whitespace changes (there are many).
Done. AFAICS
Also remove the whitespace at the end of lines.
Done. AFAICS
Make some reasonable effort to align the catalog entries for > readability.>
Done.
Any more comments?
Regards,
Gevik.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | Add comment |
|
| Recursive Queries Gregory Stark 14:12:02 |
| |
I'm looking into recursive queries and what it would take to support them in
Postgres. Is anyone else looking at this already?
Aside from the Oracle-ish syntax were there other objections to the patch as
posted a while back for 7.3 by Evgen Potemkin?
I have some ideas myself for how to go about this but I'm going to review the
existing patch first. If anyone else has ideas I would like to hear them.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
| | 13 answers | Add comment |
|
| Duplicate key violation Brian Wipf 13:55:20 |
| | I got a duplicate key violation when the following query was performed:
INSERT INTO category_product_visible (category_id, product_id)
SELECT cp.category_id, cp.product_id
FROM category_product cp
WHERE cp.product_id = $1 AND
not exists (
select 'x'
from category_product_visible cpv
where cpv.product_id = cp.product_id and
cpv.category_id = cp.category_id
);
This is despite the fact the insert is written to only insert rows
that do not already exist. The second time the same query was run it
went through okay. This makes me think there is some kind of race
condition, which I didn't think was possible with PostgreSQL's MVCC
implementation. I'm unable to duplicate the problem now and the error
only occurred once in weeks of use. This is on PostgreSQL 8.2.1
running on openSUSE Linux 10.2. Slony-I 1.2.6 is being used for
replication to a single slave database.
I'll monitor the problem and if it recurs, I'll rebuild the primary
key index. Perhaps the category_product_visible_pkey index was/is
corrupted in some way.
Brian Wipf
<brian@clickspace.com>
The exact error was:
select process_pending_changes(); FAILED!!! Message: ERROR: duplicate
key violates unique constraint "category_product_visible_pkey"
CONTEXT: SQL statement "INSERT INTO category_product_visible
(category_id, product_id) SELECT cp.category_id, cp.product_id FROM
category_product cp WHERE cp.product_id = $1 AND not exists ( select
'x from category_product_visible cpv where cpv.product_id =
cp.product_id an cpv.category_id = cp.category_id);" PL/pgSQL
function "insert_cpv" line 3 at SQL statement PL/pgSQL function
"process_mp_change" line 15 at assignment PL/pgSQL function
"process_pending_changes" line 13 at assignment
The insert_cpv(...) function and table definitions follow. I can
provide any other information required.
CREATE FUNCTION insert_cpv(
my_product_id int
) RETURNS boolean AS $$
DECLARE
BEGIN
INSERT INTO category_product_visible (category_id, product_id)
SELECT cp.category_id, cp.product_id
FROM category_product cp
WHERE cp.product_id = $1 AND
not exists (
select 'x'
from category_product_visible cpv
where cpv.product_id = cp.product_id and
cpv.category_id = cp.category_id
);
return found;
END;
$$ LANGUAGE plpgSQL;
\d category_product
Table "public.category_product"
Column | Type | Modifiers
-------------+---------+-----------
category_id | integer | not null
product_id | integer | not null
Indexes:
"x_category_product_pk" PRIMARY KEY, btree (category_id,
product_id)
"x_category_product__category_id_fk_idx" btree (category_id)
"x_category_product__product_id_fk_idx" btree (product_id)
Foreign-key constraints:
"x_category_product_category_fk" FOREIGN KEY (category_id)
REFERENCES category(category_id) DEFERRABLE INITIALLY DEFERRED
"x_category_product_product_fk" FOREIGN KEY (product_id)
REFERENCES product(product_id) DEFERRABLE INITIALLY DEFERRED
Triggers:
_ssprod_replication_cluster_logtrigger_17 AFTER INSERT OR DELETE
OR UPDATE ON category_product FOR EACH ROW EXECUTE PROCEDURE
_ssprod_replication_cluster.logtrigger('_ssprod_replication_cluster',
'17', 'kk')
category_product_trigger BEFORE INSERT OR DELETE ON
category_product FOR EACH ROW EXECUTE PROCEDURE
category_product_trigger()
\d category_product_visible
Table "public.category_product_visible"
Column | Type | Modifiers
---------------------+------------------------+-----------
category_id | integer | not null
product_id | integer | not null
Indexes:
"category_product_visible_pkey" PRIMARY KEY, btree (category_id,
product_id)
"category_product_visible__product_id_fk_idx" btree (product_id)
Triggers:
_ssprod_replication_cluster_logtrigger_18 AFTER INSERT OR DELETE
OR UPDATE ON category_product_visible FOR EACH ROW EXECUTE PROCEDURE
_ssprod_replication_cluster.logtrigger('_ssprod_replication_cluster',
'18', 'kvvvvvkvvvvvv')
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | 3 answer | Add comment |
|
| Re: Re : Re : Crash at closing [was: Crash and Dave Page 12:36:18 |
| | Laurent ROCHE wrote:> Unfortunately, as I have said earlier, I haven't found the way to reproduce the bug ... I just know that once in a while it crashes.>
Is there a way, to set up a debug/trace mode, so I can send you a debug log when it crashes ?
Under File -> Options there are some debug logging options, but I don't
expect them to help in this case - and they *will* create large logfiles.
Regards, Dave.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | Add comment |
|
| Crash at closing [was: Crash and sending report !] Laurent ROCHE 12:33:34 |
| | I am closing it, mostly by clicking on the close button (red arrow) ... but not always.
It does not always crash (tryed just now, but no closed OK) ... but I won't be able to tell what's making it crash.
I have set the debug log in the parameters so at the next crash, I will send the log.
Cheers,
L@u
The Computing Froggy
----- Message d'origine ----
De : Dave Page <dpage@postgresql.org>
: Laurent ROCHE <laurent_roche@yahoo.com>; pgAdmin Support <pgadmin-support@postgresql.org>
Envoy le : Vendredi, 19 Janvier 2007, 12h31mn 28s
Objet : Re: Re : [pgadmin-support] Crash and sending report !
Laurent ROCHE wrote:> Hi,>
The crashes happen when I close PG Admin (v 1.6.2). But because of my error messages problems (see other posts) I run PG A 1.4.3 too (for the SQL editor).> So maybe, there's conflict (freeing libraries or something like this ?).> I am not bothered about it, as it's an unusual situation to run two different versions side to side ... and it only crashes when exiting the program.>
OK, I'm losing track of the threads here - seems this one got hijacked
and I didn't notice
How do you close pgAdmin? Does it always crash, or only after performing
a specific task?
(running 2 versions in parallel should not matter)
Regards, Dave
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
___________________________________________________________________________
D couvrez une nouvelle fa on d'obtenir des r ponses toutes vos questions !
Profitez des connaissances, des opinions et des exp riences des internautes sur Yahoo! Questions/R ponses
http://fr.answers.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 3 answer | Add comment |
|
| tsearch in core patch, for inclusion Teodor Sigaev 11:09:39 |
| | We (Oleg and me) are glad to present tsearch in core of pgsql patch. In basic,
layout, functions, methods, types etc are the same as in current tsearch2 with a
lot of improvements:
- pg_ts_* tables now are in pg_catalog
- parsers, dictionaries, configurations now have owner and namespace similar to
other pgsql's objects like tables, operator classes etc
- current tsearch configuration is managed with a help of GUC variable
tsearch_conf_name.
- choosing of tsearch cfg by locale may be done for each schema separately
- managing of tsearch configuration with a help of SQL commands, not with
insert/update/delete statements. This allows to drive dependencies,
correct dumping and dropping.
- psql support with a help of \dF* commands
- add all available Snowball stemmers and corresponding configuration
- correct memory freeing by any dictionary
Work is sponsored by EnterpriseDB's PostgreSQL Development Fund.
patch: http://www.sigaev.ru/misc/tsearch_core-0.33.gz
docs: http://mira.sai.msu.su/~megera/pgsql/ftsdoc/ (not yet completed and it's
not yet a patch, just a SGML source)
Implementation details:
- directory layout
src/backend/utils/adt/tsearch - all IO function and simple operations
src/backend/utils/tsearch - complex processing functions, including
language processing and dictionaries
- most of snowball dictionaries are placed in separate .so library and
they plug in into data base by similar way as character conversation
library does.
If there aren't objections then we plan commit patch tomorrow or after tomorrow.
Before committing, I'll changes oids from 5000+ to lower values to prevent holes
in oids. And after that, I'll remove tsearch2 contrib module.
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
|
| | 44 answer | Add comment |
|
| [pgsql-patches] guid/uuid datatype Gevik Babakhani 11:01:53 |
| | Hi,
While ago (sep-2006) I sent a patch for the UUID datatype, Did anyone
have time to review it yet?
Here it is again 
Regards,
Gevik
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
|
| | 31 answer | Add comment |
|
| pgsql & transaction doubt Luca Ferrari 10:48:53 |
| | Hi all,
a little doubt that came into my mind reading the official manual (chapter 37,
page 677): "functions and trigger procedures are always executed within a
transaction established by an outer query". What does "outer" means? If I'd
like a function to be execute within a transaction should I manually begin
and end the transaction or is the procedure code always executed in a kind of
subtransaction? I mean, supposing a function raises an exception, what
happens to all the sql statement issued by the function before the exception?
Are they automatically rollbacked?
Thanks,
Luca
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| | 1 answer | Add comment |
|
| bytea performance issue Brian Stone 08:02:58 |
| | I have to store binary data in a table, ranging from 512K - 1M. I am getting very poor performance when inserting this data.
create table my_stuff (data bytea);
I then try to insert 10 1M blobs into this table using PQexecParams from C. It takes ~10 seconds to insert the 10 records.
The test is being performed locally so this is not a network issue. If I change the data I send from the client to 1M worth of text, and change the 'my_stuff.data' to TEXT, the same test takes ~2 seconds. Has anyone else seen this performance issue with bytea?
I looked at large objects but that is limited to an Oid and I will need more than 4 billion unique identifiers. Maybe not 4 billion objects at once, but I will burn through them quickly.
I looked at the byteain and byteaout functions in the postgresql source and there is quite a bit of processing going on - escape stuff. Although, this is for text-to-internal correct? If I use PGexecParams setting the format to binary, the backend should use the recv/send functions ... right?
I don't need this escaping; this information never needs to be in text form. Is it possible to create a user-defined type that's only binary? It looks like I am forced to defined an input and output function. The input function takes a CString.
Any other solutions for getting binary data into postgresql? Suggestions?
skye
---------------------------------
Finding fabulous fares is fun.
Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. |
| | 3 answer | Add comment |
|
| DBLink contrib used for replication Iannsp 08:02:16 |
| | hello all,
I like to know what you think about using dblink to construct serious syncronous and asyncronous replication.
I'm work with this idea only for test and think this is possible or almost possible because I don't know the performance for long distances but in the same network, like inside one company, the performance is good.
thanks for advanced,
--
Ivo Nascimento
Iann tech - Desenvolvendo solu es com performance e seguran a
http://www.ianntech.com.br
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 1 answer | Add comment |
|
| Restore single table from pg_dumpall. Jim Stalewski 07:42:15 |
| | Greetings,
I have been asked by a programmer to restore a single table from a
PG_DUMPALL backup.
Is this possible, and if so, would anyone be able to tell me please,
what PSQL options and switches are required? Running PostgreSQL 8.1.3.
Thanks in advance,
Jim.
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender and delete it. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company.
No employee or agent is authorized to conclude any binding agreement on behalf of Visa Lighting with another party by email without express written confirmation by an authorized representative of the Company.
Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
|
| | 4 answer | Add comment |
|
| pgAdmin III 1.6.2 debian packages for etch uploaded. Rapha l Enrici 07:21:55 |
| | -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Dear friends,
I've just uploaded pgAdmin III 1.6.2 for the upcoming debian/etch to our
developer's host. It should reach PostgreSQL mirrors in the next hours/days.
Please note that these packages are mostly statically linked against
wxWidgets 2.8.
Sarge issue:
- ------------
I'm not providing debian/sarge packages at the moment as I need to
decide what to do concerning old libraries such as libxml2 (>=2.6.18
required but 2.6.16 shipped with sarge).
Regards,
Rapha l
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFFrKtVx3NpDPptARMRAoA5AJ9HGcmanIBWLsKnBPCzEQelBvQ5OgCfWLYL
w43f2FrXpb519I7v4n6RbRQ=
=ovnF
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
| | 1 answer | Add comment |
|
| Problem loading pg_dump file Mason Hale 07:06:50 |
| | Hello --
I'm having a problem loading a recent pg_dump of our production database.
In our environment we take a monthly snapshot of our production server and
copy that to our development server so that we have a recent batch of data
to work with.
However, when trying to load the file for this month's snapshot, we are (for
the first time) seeing a slew of errors, such as:
invalid command \N
invalid command \N
ERROR: syntax error at or near ""/>\n <img alt="" style="" at character 1
LINE 1: "/>\n <img alt="" style="border: 0;
^
ERROR: syntax error at or near "padding" at character 1
LINE 1: padding: 8px 0 0 0;
^
ERROR: syntax error at or near "height" at character 1
LINE 1: height: 2px;
^
ERROR: syntax error at or near "font" at character 1
LINE 1: font-size: 1px;
^
ERROR: syntax error at or near "border" at character 1
LINE 1: border: 0;
^
ERROR: syntax error at or near "margin" at character 1
LINE 1: margin: 0;
^
ERROR: syntax error at or near "padding" at character 1
LINE 1: padding: 0;
^
invalid command \N
invalid command \N
The commands I'm using to create and load the dump are:
on production:>
pg_dump bduprod_2 | gzip > bdu_01_21_07.gz
(transfer .gz file to development server)
on dev:>
createdb -T template0 -D disk1 -e -O lss -U postgres bdu_01_21_07
gunzip -c bduprod_2-01-21-07.gz | psql bdu_01_21_07
I'm also unzipped the .gz file and tried to load it directly via
psql psql bdu_01_21_07 < psql bduprod_2-01-21-07
with the same results.
I think I might be running into the UTF8 encoding issue mentioned in this
message:
http://archives.postgresql.org/pgsql-bugs/2006-10/msg00246.php
Both the production and dev servers are UTF8.
Obviously, beyond our monthly dev snapshots, I'm concerned about the
reliability of our production server backups.
Anyone have any ideas what the problem is? Is there a way for me to tell if
it is the UTF8 encoding problem mentioned above?
Any work arounds?
thanks in advance,
Mason
|
| | 1 answer | Add comment |
|
| Linking a Postgres table on Linux to Ms Access Carter Ck 05:39:27 |
| | Hi all,
Is there any way that I can synchronize a table in Postgres on Linux with
another table in Ms Access?
The requirement of the assignment is as following:
In postgres, there is a table called message_received. Whenever we insert,
update or edit this table, the table in Ms Access should also be updated.
Advices and suggestions are all welcomed and appreciated.
Thanks
_________________________________________________________________
Get an advanced look at the new version of Windows Live Messenger.
http://get.live.com/messenger/overview
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
| | 2 answer | Add comment |
|
| triggers vs b-tree Gustavo Halperin 05:01:18 |
| | Hello I have a design question:
I have a table representing Families, and a table representing Persons.
The table Family have a row family_id as primary key.
The table Person have a row person_id as primary key and contain also a
row family_id.
As you can understand, the row family_id in a table ficha_person is not
unique, I mean is the same for all the family person's.
So my question is: If most of the time I need to find all the persons
for one asked family what is the best way to do that?
I think about two methods:
1- Making a b-tree index in ficha_person with the rows
family_id and person_id.
2 - Adding an array in the table ficha_family containing the
persons of this family. And creating a Trigger that update this array
for each person insert/delete in the table ficha_family.
So ..., what do you think? There are a better solution or what of the
above solutions is better ??
Thank you in advance,
Gustavo.
Tables:
CREATE SEQUENCE ficha_person_id_seq;
CREATE TABLE ficha_person (
person_id integer DEFAULT nextval('ficha_person_id_seq')
CONSTRAINT the_pers
on_id PRIMARY KEY,
family_id integer ,--CONSTRAINT the_family_id
.....
) WITH OIDS;
CREATE SEQUENCE ficha_family_id_seq;
CREATE TABLE ficha_family (
family_id integer DEFAULT nextval('ficha_family_id_seq')
CONSTRAINT the_fami
ly_id PRIMARY KEY,
person_id integer[], --- Optionally, instead of using b-tree index.
.....
) WITH OIDS;
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | 2 answer | Add comment |
|
| Re: [pgsql-patches] unprivileged pl install Jeremy Drake 04:48:37 |
| | On Wed, 24 Jan 2007, Tom Lane wrote:
* For an untrusted language: must be superuser to either create or use> the language (no change from current rules). Ownership of the> pg_language entry is really irrelevant, as is its ACL.> * For a trusted language:> * if pg_pltemplate.something is ON: either a superuser or the current> DB's owner can CREATE the language. In either case the pg_language> entry will be marked as owned by the DB owner (pg_database.datdba),> which means that subsequently he (or a superuser) can grant or deny> USAGE within his DB.
What happens on ALTER DATABASE ALTER OWNER? Does the ownership of the
language change to the new datdba or stay the old one?
If the CREATE LANGUAGE results in creating the handler and validation
funcs, who should own them? At the moment it is the user doing the CREATE
LANGUAGE, but what does that mean? Can they then do odd things to the
permissions of the procs, such as denying execute on them, to break other
user's usage of the language, or does the perms on a language pre-empt the
perms on the func?
What happens if pg_pltemplate.something changes after the language is
created? The datdba would continue to own the language, and can change
permissions and drop it, but could not recreate it. I assume if the
superuser wanted to revoke the ability for database owners to create that
language they would remove it from people's databases who already have it.
* if pg_pltemplate.something is OFF: must be superuser to CREATE the> language; subsequently it will be owned by you, so only you or another> superuser can grant or deny USAGE (same behavior as currently).
What if pg_pltemplate.something is OFF, the language is CREATEd by a
superuser, and then pg_pltemplate is set ON? The language is now owned by
a superuser, so the db owner could not manipulate it.
The patch I put together adds an owner to pg_language. Should there be an
ALTER LANGUAGE OWNER TO command added as well. Thinking about these
conditions I have described here, it seems to me there should be.
Or there could not be an owner for a language and who the owner is depends
on the conditions listed. But then permissions checks for languages
would depend on pg_pltemplate, which seems less than clear or ideal to me.
Besides which, when the acl is initalized from NULL to a value, it depends
on who the owner is. It would need to be changed as well when the owner
changing conditions change.
I think that an ALTER LANGUAGE OWNER TO is the proper response to these
things, and unless I hear otherwise I will attempt to add this to my
patch.
--
Checkuary, n.:
The thirteenth month of the year. Begins New Year's Day and ends
when a person stops absentmindedly writing the old year on his checks.
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| | 1 answer | Add comment |
|
| Looping recordset Furesz Peter 03:56:19 |
| | Hello,
How can I loop a PL/PgSQL recorset variable? The example:
DECLARE
v_tmp_regi RECORD;
v_tmp RECORD;
BEGIN
SELECT * INTO v_tmp_regi FROM sulyozas_futamido sf WHERE
sf.termekfajta_id=
a_termekfajta_id AND sf.marka_id=a_marka_id;
DELETE FROM sulyozas_futamido;
FOR v_tmp IN v_tmp_regi LOOP
--I would like to work here with the old recordset!
END LOOP;
^^^^^^^^^^^^^^
-- This is not working !!!
END;
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| | 1 answer | Add comment |
|
| Controlling Database Growth Mark Drago 00:41:04 |
| | -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello,
I'm using PostgreSQL to log web traffic leaving our network. This
results in the database growing to a fairly large size. This machine
will be left unattended and basically unmaintained for long stretches of
time so I need a way to limit the disk space that Postgres uses. So far
I have come up with two basic ideas:
1. Routinely run 'du' in the directory containing the PostgreSQL data,
which in my case is /var/postgresql/data and when it gets to a certain
size remove a whole bunch of the old data from the database, and run
'vacuum full; reindex database db_name; analyze;'.
The problem with this is that the vacuum could take nearly an hour to
run in some cases and there will be data that needs to get logged during
this hour. Also, the vacuum process could use disk space above what the
database is currently using and that disk space may not be available.
2. Use pgstattuple() to determine how much space is being used at any
given time and delete a bunch of old rows from the database when it is
approaching a limit.
The nice thing about this is that 'vacuum full;' does not have to be
executed in order to see the space get reclaimed. The downside is that
running pgstattuple() is much more expensive than running 'du', so the
disk space checks can't happen as often, and they can not be run at all
during the day.
I am curious to know if anyone has any other ideas as to how I can limit
the disk space that PostgreSQL uses to say 5GB. I have not looked in to
pg_autovacuum yet, but from what I have read about it it does not seem
to be the answer to this problem. Has anyone else had to do such a
thing before? Does anyone have any ideas on how to do this better?
Thanks,
Mark Drago
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org
iD8DBQFFuSGy2ovBrIOxiiARAjmeAKCgmN4fNWTv1ZTgkCQZCeAAgYdLyQCgwZsb
uqveC3xd97nWNg2ty2MCs0M=
=dTca
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
|
| | 2 answer | Add comment |
|
| Re: ruby-postgres module needs work, maintainer Bruce Momjian 00:41:04 |
| | Jeff Davis wrote:> Hi,>
The ruby-postgres module needs some work, and I have some patches.> However, the maintainer Dave Lee is not replying to my emails. > Does anyone on this list know how to contact him? If not, I'll try to> get my fixes submitted through the Ruby community somehow.> If you're a user of this module, please let me know, and let me know if> you have any problems with it.
I haven't heard anything from the plruby guy in a while. You might need
to become the new maintainer.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
| | 4 answer | Add comment |
|
| escape string for pgsql (using jdbc/java)? Tobias Thierer 00:41:04 |
| | Hi,
I'm writing a servlet that gets a few strings and puts them into a pgsql
database. In assembling an insert statement such as
INSERT INTO table column1='value1' column2='value2'
etc., of course I have to make sure an attacker can't put things into value1
that will breaky my system (such as something that contains a ' which will
then be interpreted as terminating the string). In other words, I have to
escape value* so that it's safe to use in an sql statement (more
specifically inside a string).
I was previously using MySQL and escaped strings following the document at:
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html
But I couldn't find a corresponding specification for pgsql. The only way of
doing this through JDBC that I'm aware of is to prepare a statement first,
which just seems wrong because my insert statement is generated dynamically
and executed exactly once (the subset of the columns for which a value is
actually set change every time the code is run).
So,
1.) Is there a built-in method somewhere in the jdbc driver that escapes
strings and makes them safe to use in an SQL statement (inside a
string)?
2.) Which characters do I need to escape for pgsql? Is ' the only one,
and I need to escape it as '' ? Do I need to escape \ ? Will I need to
escape all the characters that I escaped for MySQL? Where can I find
out more?
Cheers,
Tobias
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | 2 answer | Add comment |
|
