|
 |
| Recent blog posts: | |
|
| They have birthday today: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Friday, 26 January 2007
|
| [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 |
|
| 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 |
|
| 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 |
|
| [pgsql-patches] uuid patch 2.0 (8.3devel) Gevik Babakhani 00:41:04 |
| | Folks,
Hereby the version 2.0 of the uuid datatype patch with modifications
commented by Neil.
- the uuid.h has been cleaned.
the declarations have been moved to uuid.c
- the text_uuid() and varchar_uuid() have been refactored.
- all uuid explicit functions are moved to uuid.c and made local.
* this patch has been tested on 8.3devel, the snapshot of 25-Jan-07
* this patch uses 28 new oids. I have assigned the oids from 2950.
If you need to change the oids, do not do this manually. I have a script
that does that. Just provide me 28 unused oids and I will generate a new
patch.
Please provide comments.
Regards,
Gevik
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 1 answer | Add comment |
|
| Re: [pgsql-patches] [HACKERS] unprivileged contrib and pl install Jeremy Drake 00:41:04 |
| | On Wed, 24 Jan 2007, Tom Lane wrote:
Jeremy Drake <pgsql@jdrake.com> writes:> > On Wed, 24 Jan 2007, Jeremy Drake wrote:> >> That would be great, and also it would be great to be able to CREATE> >> LANGUAGE as a regular user for a trusted pl that is already> >> compiled/installed.> Something like the attached (simple) change to allow CREATE LANGUAGE by> > unprivileged users for trusted languages already present in pg_pltemplate.> If it were merely a matter of removing an error check I think we would> have done it already. However, pltemplate will have all the languages> in it whether the DBA wants to allow them to be used or not; so I'd say> that there really needs to be *some* sort of privilege check here.> What that is and how to implement it are the hard parts.
So I guess it depends on what you mean by "DBA". Perhaps the database
owner? Or some new privilege type (GRANT CREATE ON LANGUAGE ...? Or GRANT
CREATE LANGUAGE ON DATABASE...?) that the db owner has by default?
--
7:30, Channel 5: The Bionic Dog (Action/Adventure)
The Bionic Dog drinks too much and kicks over the National
Redwood Forest.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 6 answers | 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 |
|
| replication choices Chris Ochs 00:41:04 |
| | From reading everything I can find, it seems that the Mammoth replicator and
the ErServer are the only two available, working alternatives for PG 7.4.
Can someone confirm or deny this?
Chris
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
|
| | 4 answer | Add comment |
|
| Re: [pgsql-patches] Win32 WEXITSTATUS too Bruce Momjian 00:41:04 |
| | bruce wrote:> Tom Lane wrote:> > Alvaro Herrera <alvherre@commandprompt.com> writes:> > > Bruce Momjian wrote:> > >> OK, maybe /doc or src/tools. A more radical approach would be to put> > >> the list in our documentation, or have initdb install it.> >
Why not put it in techdocs or some such?> > I think we've learned by now that putting copies of other peoples' code> > in our tree isn't such a hot idea; what is going to cause it to be> > updated when things change? How do you know the values are even the> > same across all the Windows versions we support?> > Basically this whole idea is misconceived. Just print the number and> > have done.> And how do people interpret that number?
Ah, I found something:
http://support.microsoft.com/kb/259693
Someone on IRC says that is kernel mode only, and is looking for a
user-mode version, so we would be able to print out a meaningful message
rather than a hex value that has to be looked up.
--
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 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
|
| | 50 answers | Add comment |
|
|
