|
 |
| Recent blog posts: | |
|
| They have birthday today: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Friday, 26 January 2007
|
| [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 |
|
| 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 |
|
| BUG #2917: spi_prepare doesn't accept typename aliases such as 'integer' Andrew Dunstan 00:24:54 |
| |
The author of this bug was good enough to send me a copy, since I don't
normally read the -bugs list. It can now be found at
http://archives.postgresql.org/pgsql-bugs/2007-01/msg00111.php .
I dug into it a bit and found that pltcl and plpython appear to use
almost identical code, but only pltcl has this limitation documented.
I'm inclined to say we should document this for plperl and plpython for
stable releases and remove the limitation for all three for 8.3. I see
that SQL level prepare calls regprocin() to resolve type names, so maybe
we should that for the PLs when calling SPI_prepare as well.
Alternatively, should we adjust things lower down (e.g. in
typenameType() or LookupTypeName() ) ?
Comments?
cheers
andrew
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 1 answer | Add comment |
Thursday, 25 January 2007
|
| Re: shared_buffers and shmall,shmmax Tom Lane 23:58:20 |
| | shmall is 65536 page
And how big is a page?
regards, tom lane
---------------------------(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 |
|
| Scanner/Parser question - what does _P imply? Guest 23:34:47 |
| | I can't find an authoritative answer to this question.
Many of the keywords listed in keywords.c are defined with symbolic
names that end in '_P' (underscore P).
What differentiates those keywords from the other keywords? What does
the 'P' stand for?
Are those PostgreSQL-specific keywords (i.e. keywords not defined by the
SQL standard)?
Thanks.
-- Korry
--
Korry Douglas korryd@enterprisedb.com
EnterpriseDB http://www.enterprisedb.com
|
| | 4 answer | Add comment |
|
| loop plpgsql recordset variable Furesz Peter 23:28:03 |
| | 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 |
|
| Error while creating stored procedure through ODBC connection Gary Greenberg 23:24:07 |
| | I am trying to generate a database by running DDL script through the ODBC
connection.
It went through almost to the end and then barfed on creating procedure.
Here is a snippet from the generation log:
*** Executing statement 311:
CREATE FUNCTION mask_new_permission () RETURNS trigger AS
$mask_new_permission$
begin
new.PERMISSION_MASK := coalesce(select 2*max(PERMISSION_MASK) from
permissions, 1)
*** Execution of statement failed:
ERROR: unterminated dollar-quoted string at or near "$mask_new_permission$
begin
new.PERMISSION_MASK := coalesce(select 2*max(PERMISSION_MASK) from
permissions, 1)";
Error while executing the query
SQLSTATE = 42601
*** Executing statement 312:
return new
*** Execution of statement failed:
ERROR: syntax error at or near "return";
Error while executing the query
SQLSTATE = 42601
*** Executing statement 313:
end
*** Statement successfully executed.
*** Executing statement 314:
$mask_new_permission$ LANGUAGE plpgsql
*** Execution of statement failed:
ERROR: unterminated dollar-quoted string at or near "$mask_new_permission$
LANGUAGE plpgsql";
Error while executing the query
SQLSTATE = 42601
*** Executing statement 315:
create trigger permissions_bit before insert on permissions
for each row execute procedure mask_new_permission()
*** Execution of statement failed:
ERROR: function mask_new_permission() does not exist;
Error while executing the query
SQLSTATE = 42883
When I am executing the same piece of code from psql prompt it is going
through without complaining:
ops2=> CREATE FUNCTION mask_new_permission () RETURNS trigger AS
$mask_new_permission$
ops2$> begin
ops2$> new.PERMISSION_MASK := coalesce(select 2*max(PERMISSION_MASK)
from permissions, 1);
ops2$> return new;
ops2$> end;
ops2$> $mask_new_permission$ LANGUAGE plpgsql;
CREATE FUNCTION
ops2=>
ops2=>
ops2=> create trigger permissions_bit before insert on permissions
ops2-> for each row execute procedure mask_new_permission();
CREATE TRIGGER
Can anyone explain it to me, please?
Thx,
Gary
BTW, ODBC driver is PSQLODBC30A.dll
|
| | 1 answer | Add comment |
|
| Postgres v MySQL 5.0 Duncan Garland 22:53:40 |
| | How do you position Postgres against MySQL 5.0?
MySQL is more popular but I've always regarded it as a bit lightweight. I'm
told that this is no longer the case with 5.0. Why choose Postgres over
MySQL 5.0?
Thanks.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 94 answer | Add comment |
|
| [pgsql-patches] setlocal to build.bat Magnus Hagander 22:50:19 |
| | This patch adds a setlocal command to the beginning of build.bat. This
is required to deal with buildenv.bat properly, so that PATH (for
example) doesn't expand infintly.
Per report from Joachim Wieland.
//Magnus
Index: src/tools/msvc/build.bat
===================================================================
RCS file: /projects/cvsroot/pgsql/src/tools/msvc/build.bat,v
retrieving revision 1.4
diff -c -r1.4 build.bat
*** src/tools/msvc/build.bat16 Jan 2007 21:43:19 -00001.4
--- src/tools/msvc/build.bat25 Jan 2007 15:08:26 -0000
***************
*** 1,4 ****
--- 1,5 ----
@echo off
+ SETLOCAL
SET STARTDIR=%CD%
if exist src\tools\msvc\buildenv.bat call src\tools\msvc\buildenv.bat
if exist buildenv.bat call buildenv.bat
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 3 answer | Add comment |
|
| Basic connection problem Paul B. Anderson 22:28:38 |
| | I'm just trying to get going with an ODBC interface to Postgresql 8.1.5
on RHEL 3.0. I'm using the database without problems via psql, ecpg,
and JDBC.
I've hit the same problem using both iODBC and unixODBC driver
managers. It get the following when I try to connect to the database:
[08001][unixODBC]could not translate host name "port=5432" to address:
Name or service not known
[ISQL]ERROR: Could not SQLConnect
I've experimented with my ~/.odbc.ini file with and without Port = 5432
but can't seem to figure how port=5432 is even getting into the connect
string.
I've got psqlodbc 08.01.0200. I had to make a couple of small coding
changes to build it for iODBC but unixODBC worked fine with psqlodbc
code as downloaded.
I have unicode support turned on in the driver. My database uses UTF8.
Surely this must be a common problem. What am I doing wrong?
Paul
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 2 answer | Add comment |
|
| Re: Proposal: allow installation of any contrib module Joshua D. Drake 21:57:00 |
| | The problem with this proposal is that the ISPs aren't the ones running> configure --- these days, most people are running prebuilt packages> (RPMs or DEBs or what have you). So what you are hoping is that the> packagers will choose to do this and thereby force these modules into> the "standard" configuration for everybody using their packages. I'm> not sure that the packagers will change ... well maybe Gentoo will,> but not anyone with more conservative policies ... and I'm pretty sure> any who do will get push-back from people who still won't trust contrib.
Well perhaps it is time to trim Contrib even further. E.g;
Why is ltree still in contrib? What prevents it from being in core?
Why is pgcrypto,pgstattuple and pg_freespacemap in contrib?
I would almost think it makes sense to have:
/modules - This is for things that are indeed modules they will be
installed by default but are not *internal* like sum() or generate_series()
/contrib - This is for things like adminpack, dblink, pgbench
Sincerely,
Joshua D. Drake
regards, tom lane> ---------------------------(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>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 19 answers | Add comment |
|
| HOWTO? if it exists, increment it, else, insert it Brian Neu 20:25:07 |
| | I can't seem to get CASE to work for me here and every example that I see is using CASE in a completely different way.
have a table:
day(default CURRENT_DATE), user, count_of_a, count_of_b
index is ("day", user)
What is the best postgres way to handle updating this table?
I basically need to say,
if (day,user) exists, then increment count A
else insert day, user, and count_a = 1.
thank you in advance
|
| | 1 answer | Add comment |
|
| relationship in a table Kai Schlamp 19:46:35 |
| | Hello!
What is the best way to find out the relationship of two columns in a
table?
I want to know if it is 1:1, 1:N, or M:N. Is there an easy way to do
this with a SQL statement?
Best regards,
Kai
---------------------------(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 |
|
| ECPG buglet? Magnus Hagander 19:26:12 |
| | Looking at the VC warnings, I find that in ECPGDynamicType()
(typename.c in ecpglib), the default branch of the case statement
returns "-type". But the function returns "unsigned int", so returning a
negative value seems very strange to me.
At least it throws a warning in VC++.
//Magnus
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 1 answer | Add comment |
|
| New feature proposal Marc Munro 19:22:46 |
| | Veil http://pgfoundry.org/projects/veil is currently not a very good
Postgres citizen. It steals what little shared memory it needs from
postgres' shared memory using ShmemAlloc().
For Postgres 8.2 I would like Veil to be a better citizen and use only
what shared memory has been reserved for postgres add-ins.
So, I would like to propose:
1) that a new shared memory area for add-ins be created
2) that it should be accessible only to add-ins specifically requesting
its use
3) that the size of this add-in memory be controlled through a new GUC
setting
If this proposal is acceptable, I'd like some suggestions for naming the
GUC. If not we will be stuck with add_in_shmem, specified in Kbytes.
My expectation is that this would be a very small change and one that I
am probably competent to make myself, with a little review from my
friends.
__
Marc
|
| | 13 answers | Add comment |
|
| Proposal: allow installation of any contrib module simultaneously with Postgres itself Nikolay Samokhvalov 19:22:33 |
| | Discussion "tsearch in core patch, for inclusion" shows
(http://archives.postgresql.org/pgsql-hackers/2007-01/msg01165.php and
following http://archives.postgresql.org/pgsql-hackers/2007-01/msg01186.php)
that there are some problems with contrib promotion and expansion.
I've encountered with bad awareness and some kind of fears (due to
"possible security holes" and similar reasons) regarding contrib
modules. For example, hstore is very good thing that helps to resolve
many issues (e.g. logging tasks), but not many people know are aware
of it, and there are very few hosting providers which include hstore
to Postgres installation.
So, it would be really good if documentation and the main website
itself include more information describing the modules (maybe to
review README files and include them all in the docs?).
But I want to propose something more. It's clear that some ISPs are
afraid of contrib modules installation, many of which are very useful
and have reliable code. But, those ISPs are not afraid to install,
say, PHP with a dozen modules (extensions). Why? Besides the fact that
PHP modules are very good described in the main PHP manual, I see very
simple reason: to install a contrib module you must go to contrib dir
and run _another_ "make install" (wth following "psql .. < module.sql"
surely), while to install PHP extension you should only add
"--with-modulename" to the configuration command.
Well, my proposal is simple:
1. Change default behaviour of <MODULE_NAME>.sql file so it will be
installed in <MODULE_NAME> schema instead of "public" (e.g., "hstore"
schema will contain all hstore relations and functions).
2. Allow running configure with "--with-<MODULE_NAME>" (or
"--enable-<MODULE_NAME>") to include compilation of module's libraries
simultaneously with Postgres itself and including running of module's
registration SQLs (from that .sql files) simultaneously with cluster
creation (in other words, with inidb invocation -- this will add
"<MODULE_NAME>" schema to template0).
This will simplify the procedure of starting to use contrib modules
and will help to promote the modules themselves (and, as a result,
some PostgreSQL's advanced features). I think many projects have
similar behaviour with regard to their extensions. And ISPs will
install PostgreSQL with a bundle of useful and "trusted" extensions,
simply running "./configure --with-tsearch2 --with-hstore
--with-dblink" (actually, I hope that tsearch2 will be in core, but
this is really good example at the moment  ) - like they do with
PHP, Apache and other software.
Let's make the usage of contrib modules more user-friendly.
--
Best regards,
Nikolay
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 2 answer | Add comment |
|
| SQL Newbie Question Inoqulath 19:08:01 |
| | Hello Folks
Have a look at this Table:
CREATE TABLE foo(
id serial,
a_name text,
CONSTRAINT un_name UNIQUE (a_name));
Obviously, inserting a string twice results in an error (as one would
expect). But: is there any known possibility to ingnore an errorneous
INSERT like SQLite's "conflict algorithm" (SQLite:"INSERT OR
[IGNORE|ABORT] INTO foo [...]")?
I tried to use a trigger before INSERT takes place, but it seems that
before firing a trigger the constraints are checked...
Background: I'd like to INSERT a few thousand lines in one transaction,
where some values will be appear twice.
thx in Advance
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 4 answer | Add comment |
|
|
