Thursday, 25 January 2007
|
| pg_trigger.tgargs needs detoast Kenji Kawamura 07:18:34 |
| | Hello,
This patch fixes a bug of case of extraction of pg_trigger.tgargs.
There was a problem when we used a long argument in defining trigger,
possibly resulting in a server crash.
Example:
We defined a CREATE TRIGGER such as follows and registered trigger.
In this case, the argument value which we received in the trigger
procedure was not right.
CREATE TRIGGER trigger_test BEFORE INSERT OR UPDATE ON sample FOR EACH
ROW EXECUTE PROCEDURE sample_trig('XXX...(more than 1823 characters)');
The trigger procedure which receives the argument:
Datum sample_trig(PG_FUNCTION_ARGS)
{
TriggerData* trigdata = (TriggerData*)fcinfo->context;
char** args = trigdata->tg_trigger->tgargs;
int nargs = trigdata->tg_trigger->tgnargs;
int i;
for (i = 0; i < nargs; i++) {
elog(LOG, "%s", args[i]);
}
...
}
Result:
Before: LOG: (the character that is not right, for example '%')
After : LOG: XXX...(more than 1823 characters)
Regards,
---
Kenji Kawamura
NTT Open Source Center, Japan
Index: src/backend/commands/tablecmds.c
===================================================================
--- src/backend/commands/tablecmds.c(HEAD)
+++ src/backend/commands/tablecmds.c(modified)
@@ -1800,8 +1800,7 @@
* line; so does trigger.c ...
*/
tgnargs = pg_trigger->tgnargs;
-val = (bytea *)
-DatumGetPointer(fastgetattr(tuple,
+val = DatumGetByteaP(fastgetattr(tuple,
Anum_pg_trigger_tgargs,
tgrel->rd_att, &isnull));
if (isnull || tgnargs < RI_FIRST_ATTNAME_ARGNO ||
Index: src/backend/commands/trigger.c
===================================================================
--- src/backend/commands/trigger.c(HEAD)
+++ src/backend/commands/trigger.c(modified)
@@ -906,8 +906,7 @@
char *p;
inti;
-val = (bytea *)
-DatumGetPointer(fastgetattr(htup,
+val = DatumGetByteaP(fastgetattr(htup,
Anum_pg_trigger_tgargs,
tgrel->rd_att, &isnull));
if (isnull)
Index: src/backend/utils/adt/ruleutils.c
===================================================================
--- src/backend/utils/adt/ruleutils.c(HEAD)
+++ src/backend/utils/adt/ruleutils.c(modified)
@@ -521,8 +521,7 @@
char *p;
inti;
-val = (bytea *)
-DatumGetPointer(fastgetattr(ht_trig,
+val = DatumGetByteaP(fastgetattr(ht_trig,
Anum_pg_trigger_tgargs,
tgrel->rd_att, &isnull));
if (isnull)
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
|
| |  3 answer | Add comment |
|
| [ psqlodbc-Bugs-1001172 ] Problem with ADO.RecordCount Guest 06:55:47 |
| | Bugs item #1001172, was opened at 2007-01-02 17:32
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1001172&group_id=1000125
Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Paul R ttgerding (paul1234)
Assigned to: Nobody (None)
Summary: Problem with ADO.RecordCount
Initial Comment:
Problem in ODBC and ADO
DatenbankInfos:
ADO Version: 2.8
DBMS Name: PostgreSQL
DBMS Version: 8.1.4
OLE DB Version: 02.00
Provider Name: MSDASQL.DLL
Provider Version: 02.81.1117.0
Transaktion: 8
ODBC Name: StudWerkPG
Driver Name: PSQLODBC35W.DLL
Driver Version: 08.02.0203
Driver ODBC Version: 03.51
The Select in PGAdmin 1.6 works.
1362 lines result.
(screenshot)
In Visual basic via ODBC and ADO it fails with:
-2147467259
Der Datenprovider oder ein anderer Dienst gab den Status E_FAIL zur ck.
rsstu.source =
select ISW_STUNDEN.IDMELDUNG,sum(BruttoBetrag) as summ from ISW_STUNDEN,ISW_MELDUNGEN where IDOBJEKT=2 and OBJEKTTYP='GEBAEUDE' and ISW_MELDUNGEN.IDMELDUNG=ISW_STUNDEN.IDMELDUNG group by ISW_STUNDEN.IDMELDUNG
The Code:
'Stundenkosten
If rsStu.RecordCount > 0 Then E_Fail with RecordCount
rsStu.MoveFirst ' an den Anfang f r die erste Suche MoveFirst works ok
rsStu.Find "IDMELDUNG=" & .TextMatrix(i, 0) E_Fail with Find
If rsStu.EOF = False Then ' Objekt nicht vorhanden, deswegen nochmal von vorne durch alle Datens tze suchen
'Betr = IIf(IsNull(rsStu(1) = True), 0, rsStu(1))
Betr = DbGetSummenValue(rsStu, 1, , 0) 'IIf(IsNull(rsStu(1) = True), 0, rsStu(1))
End If
End If
Test with rsStu.MoveLast fails also
Important: When I use an other where-clause and the resultset have per example 123 rows, all is OK !!
What can I do?
Thanks
Paul
----------------------------------------------------------------------
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1001172&group_id=1000125
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
|
| | 3 answer | Add comment |
|
| Help with Degree Work Werner Echezuria 06:24:07 |
| | Hello,
I've suscribed to this mailing list for help, I will work on a
Specialization Degree Thesis, this will be a PostgreSQL implementation of
fsql, or fuzzy querys.
http://www.lcc.uma.es/~ppgg/FSQL.html, this is a link to a webpage who made
this in Oracle, but it's not inside of course, because it was made for other
people.
My work will be make this in the PostgreSQL Kernell, so i'll appreciate all
help you can bring.
Thanks.
|
| | 1 answer | Add comment |
|
| Idle in transaction - Explination .. Weslee Bilodeau 06:06:36 |
| | Where I work I'm in charge of more then a few PostgreSQL databases.
I understand why idle in transaction is bad, however I have some
developers who I'm having a real difficult time fully explaining to them
why its bad.
Oh, and by bad I mean they have transactions that are sitting idle for
6+ hours at a time.
Mainly because they don't speak very good English, and my words like
MVCC and VACUUM have them tilting their heads wondering what language
I'm speaking.
I've tried searching the mailing lists for a good explanation, but
haven't really found one thats easy to translate.
They are Japanese, but I don't speak Japanese, so finding any resource
in Japanese that explains it is beyond my ability.
Would anyone happen to have a simple explanation, or a page online thats
written in Japanese that I can pass off that might explain why this is bad?
Is there a Wiki somewhere that says "101 ways to cause your DBA an
aneurysm" that covers things like this? 
Weslee
---------------------------(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 |
|
| Problem with replace Slawosz 05:51:19 |
| | Hello,
I have to relpace string
http://example.com/index.php?module=articles&id= to string /module/ in
whole column in table. How exacly use replace? I noob in postgres, I
think it could be quite easy.
Please, help
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 1 answer | Add comment |
|
| TODO list has removed developer names Bruce Momjian 05:51:13 |
| | I have removed the developer names from the bottom of the TODO list now
that URLs are used to reference discussions. The URLs are much more
accurate than putting names on items.
--
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
|
| | Add comment |
|
| Re: [pgsql-patches] pg_dumpall default database Dave Page 05:46:45 |
| | Peter Eisentraut wrote:> Am Montag, 15. Januar 2007 14:44 schrieb Dave Page:>> Per discussion on -hackers, the attached patch allow the default>> database to be specified on the pg_dumpall command line, eg.>>
pg_dumpall [options...] [dbname]> I think this should be a separate option. Otherwise it would be too easy to > confuse this with how pg_dump treats the argument.
You're thinking the user might expect it dump all of that database? Not
sure I agree with that, but I'm not wedded to the syntax. Any other
opinions?
Regards, Dave.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
| | 8 answers | Add comment |
|
| [pgsql-patches] Allow dumping of roles or tablespaces Dave Page 05:30:43 |
| | Per discussion on -hackers, the attached patch introduces an optional
parameter to pg_dumpall's -g (--globals-only) option to allow roles or
tablespaces to be dumped on their own.
eg.
pg_dumpall -g -- Dump roles and tablespaces per current behaviour
pg_dumpall -gr -- Dump roles only (or users and groups)
pg_dumpall -gt -- Dump tablespaces only
Regards, Dave.
Index: doc/src/sgml/ref/pg_dumpall.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v
retrieving revision 1.58
diff -c -r1.58 pg_dumpall.sgml
*** doc/src/sgml/ref/pg_dumpall.sgml7 Oct 2006 20:59:04 -00001.58
--- doc/src/sgml/ref/pg_dumpall.sgml12 Jan 2007 14:01:10 -0000
***************
*** 130,140 ****
</varlistentry>
<varlistentry>
! <term><option>-g</option></term>
! <term><option>--globals-only</option></term>
<listitem>
<para>
! Dump only global objects (users and groups), no databases.
</para>
</listitem>
</varlistentry>
--- 130,145 ----
</varlistentry>
<varlistentry>
! <term><option>-g[<replaceable class="parameter">r</replaceable>|<replaceable class="parameter">t</replaceable></option></term>
! <term><option>--globals-only[=<replaceable class="parameter">r</replaceable>|<replaceable class="parameter">t</replaceable></option></term>
<listitem>
<para>
! Dump only global objects (roles and/or tablespaces), no databases.
! The <replaceable class="parameter">r</replaceable> parameter will
! cause only roles to be dumped, and the
! <replaceable class="parameter">t</replaceable> parameter will cause
! only tablespaces to be dumped. If no parameter is specified, all
! global object types will be dumped.
</para>
</listitem>
</varlistentry>
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.86
diff -c -r1.86 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c5 Jan 2007 22:19:48 -00001.86
--- src/bin/pg_dump/pg_dumpall.c12 Jan 2007 14:01:12 -0000
***************
*** 78,83 ****
--- 78,85 ----
boolforce_password = false;
booldata_only = false;
boolglobals_only = false;
+ boolroles_only = false;
+ booltablespaces_only = false;
boolschema_only = false;
PGconn *conn;
intencoding;
***************
*** 91,97 ****
{"inserts", no_argument, NULL, 'd'},
{"attribute-inserts", no_argument, NULL, 'D'},
{"column-inserts", no_argument, NULL, 'D'},
! {"globals-only", no_argument, NULL, 'g'},
{"host", required_argument, NULL, 'h'},
{"ignore-version", no_argument, NULL, 'i'},
{"oids", no_argument, NULL, 'o'},
--- 93,99 ----
{"inserts", no_argument, NULL, 'd'},
{"attribute-inserts", no_argument, NULL, 'D'},
{"column-inserts", no_argument, NULL, 'D'},
! {"globals-only", optional_argument, NULL, 'g'},
{"host", required_argument, NULL, 'h'},
{"ignore-version", no_argument, NULL, 'i'},
{"oids", no_argument, NULL, 'o'},
***************
*** 161,167 ****
pgdumpopts = createPQExpBuffer();
! while ((c = getopt_long(argc, argv, "acdDgh:ioOp:sS:U:vWxX:", long_options, &optindex)) != -1)
{
switch (c)
{
--- 163,169 ----
pgdumpopts = createPQExpBuffer();
! while ((c = getopt_long(argc, argv, "acdDg::h:ioOp:sS:U:vWxX:", long_options, &optindex)) != -1)
{
switch (c)
{
***************
*** 181,186 ****
--- 183,203 ----
case 'g':
globals_only = true;
+ if (optarg)
+ {
+ if (strcmp(optarg, "r") == 0)
+ roles_only = true;
+ else if(strcmp(optarg, "t") == 0)
+ tablespaces_only = true;
+ else
+ {
+ fprintf(stderr,
+ _("%s: invalid -g option -- %s\n"),
+ progname, optarg);
+ fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
+ exit(1);
+ }
+ }
break;
case 'h':
***************
*** 332,349 ****
printf("SET escape_string_warning = 'off';\n");
printf("\n");
! /* Dump roles (users) */
! dumpRoles(conn);
! /* Dump role memberships --- need different method for pre-8.1 */
! if (server_version >= 80100)
! dumpRoleMembership(conn);
! else
! dumpGroups(conn);
! /* Dump tablespaces */
! if (server_version >= 80000)
! dumpTablespaces(conn);
/* Dump CREATE DATABASE commands */
if (!globals_only)
--- 349,372 ----
printf("SET escape_string_warning = 'off';\n");
printf("\n");
! if (!tablespaces_only)
! {
! /* Dump roles (users) */
! dumpRoles(conn);
! /* Dump role memberships --- need different method for pre-8.1 */
! if (server_version >= 80100)
! dumpRoleMembership(conn);
! else
! dumpGroups(conn);
! }
! if (!roles_only)
! {
! /* Dump tablespaces */
! if (server_version >= 80000)
! dumpTablespaces(conn);
! }
/* Dump CREATE DATABASE commands */
if (!globals_only)
***************
*** 381,387 ****
printf(_(" -c, --clean clean (drop) databases prior to create\n"));
printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n"));
printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
! printf(_(" -g, --globals-only dump only global objects, no databases\n"));
printf(_(" -o, --oids include OIDs in dump\n"));
printf(_(" -O, --no-owner skip restoration of object ownership\n"));
printf(_(" -s, --schema-only dump only the schema, no data\n"));
--- 404,410 ----
printf(_(" -c, --clean clean (drop) databases prior to create\n"));
printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n"));
printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n"));
! printf(_(" -g, --globals-only=[r|t] dump only global objects, no databases. Optionally restrict to roles or tablespaces\n"));
printf(_(" -o, --oids include OIDs in dump\n"));
printf(_(" -O, --no-owner skip restoration of object ownership\n"));
printf(_(" -s, --schema-only dump only the schema, no data\n"));
---------------------------(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
|
| | 6 answers | Add comment |
|
| COPY with no WAL, v2 Simon Riggs 05:17:38 |
| | VERSION 2, with all changed made as requested to date.
As discussed on -hackers, its possible to avoid writing any WAL at all
for COPY in these circumstances:
http://archives.postgresql.org/pgsql-hackers/2006-10/msg01172.php
and again recently.
BEGIN;
CREATE TABLE foo..
COPY foo...
COMMIT;
BEGIN;
TRUNCATE foo..
COPY foo...
COMMIT;
The enclosed patch implements this, as discussed. There is no user
interface to enable/disable, just as with CTAS and CREATE INDEX; no
docs, just code comments.
This plays nicely with the --single-transaction option in psql to allow
fast restores/upgrades.
YMMV but disk bound COPY will benefit greatly from this patch, some
tests showing 100% gain. COPY is still *very* CPU intensive, so some
tests have shown negligible benefit, fyi, but that isn't the typical
case.
While testing this, I realised something: small COPY commands get no
benefit at all, but larger ones do. When we do a small normal COPY the
data stays in cache, but the WAL is written to disk and fsynced. When we
do a small fast COPY, no WAL is written, but the data is written to disk
and fsynced. With COPY, WAL and data are roughly same size, hence no I/O
benefit. With larger COPY statements, benefit is very substantial.
Applies cleanly to CVS HEAD, passes make check.
I enclose a test case that shows whether the test has succeeded by
reading the WAL Insert pointer before/after each COPY. This has been
written in such a way that we could, if we wanted to, include a new
regression test for this. There is a function that returns an immutable
value if the test passes, rather than simply showing the WAL insert
pointer which would obviously vary between tests. The tests enclosed
here *also* include the WAL insert pointer so you can manually/visibly
see that the enclosed patch writes no WAL at appropriate times.
psql -f copy_nowal_prep.sql postgres
psql -f copy_nowal_test.sql postgres
Do we want an additional test case along these lines?
Agreed doc changes for Performance Tips forthcoming.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 5 answers | Add comment |
|
| Cannot Restart PostgreSQL-8.1.4 Rich Shepard 04:52:24 |
| | I had a problem with SQL-Ledger running on the local httpd that traced
back to some crufty old libpg.so* from 2003 and 2004 in /usr/local/lib. I
removed those (saved them, actually), ran ldconfig, then restarted both
httpd and postgresql. Unfortunately, the latter really has not started
despite indicating on the console that it has.
Postgres-8.1.4 installed.
Here're the libaries in /usr/lib/:
[rshepard@salmo ~]$ ll /usr/lib/libpq*
-rw-r--r-- 1 root root 149728 2006-05-24 15:06 /usr/lib/libpq.a
lrwxrwxrwx 1 root root 12 2006-07-06 17:19 /usr/lib/libpq.so ->
libpq.so.4.1*
lrwxrwxrwx 1 root root 12 2006-01-27 10:22 /usr/lib/libpq.so.3 ->
libpq.so.3.1*
-rwxr-xr-x 1 root root 110586 2006-01-26 09:49 /usr/lib/libpq.so.3.1*
lrwxrwxrwx 1 root root 12 2006-07-06 17:19 /usr/lib/libpq.so.4 ->
libpq.so.4.1*
-rwxr-xr-x 1 root root 111532 2006-05-24 15:06 /usr/lib/libpq.so.4.1*
And, in case postgres is looking in /usr/local/lib/ it has:
[rshepard@salmo ~]$ ll /usr/local/lib/libpq*
lrwxrwxrwx 1 root root 21 2007-01-24 10:38 /usr/local/lib/libpq.so ->
/usr/lib/libpq.so.4.1*
lrwxrwxrwx 1 root root 21 2007-01-24 10:35 /usr/local/lib/libpq.so.3 ->
/usr/lib/libpq.so.3.1*
lrwxrwxrwx 1 root root 21 2007-01-24 10:38 /usr/local/lib/libpq.so.4 ->
/usr/lib/libpq.so.4.1*
When I run '/etc/rc.d/rc.postgresql start' it returns 'Starting
PostgreSQL: ok', but '/etc/rc.d/rc.postgresql status' returns 'pg_ctl:
neither postmaster nor postgres running' which is true.
I don't know what I did to break the installation, but I would greatly
appreciate help getting it running again ASAP.
TIA,
Rich
--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
---------------------------(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
|
| | 11 answers | Add comment |
|
| Re: [pgsql-patches] [HACKERS] unprivileged contrib and pl install (formerly tsearch Jeremy Drake 03:50:58 |
| | On Wed, 24 Jan 2007, Jeremy Drake wrote:
On Wed, 24 Jan 2007, Martijn van Oosterhout wrote:> Something I've wondered about before is the concept of having installed> > Modules in the system. Let's say for example that while compiling> > postgres it compiled the modules in contrib also and installed them in> > a modules directory.> > Once installed there, unpriviledged users could say "INSTALL foo" and> > it would install the module, even if they do not have the permissions> > to create them themselves.> 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.
I'm not quite sure how one would go about doing the module thing, I think
that would be more complex. Something simple like allowing creation of C
language functions in libraries in $libdir would probably not be
sufficient, because an unprivileged user could create functions that have
the wrong paramters or return values and crash things pretty good that
way. Any ideas how this would work? Perhaps a sql script in
sharedir could be run by the backend as though by a superuser...
--
Ed Sullivan will be around as long as someone else has talent.
-- Fred Allen
Index: src/backend/commands/proclang.c
===================================================================
RCS file: /home/jeremyd/local/postgres/cvsuproot/pgsql/src/backend/commands/proclang.c,v
retrieving revision 1.71
diff -c -r1.71 proclang.c
*** src/backend/commands/proclang.c22 Jan 2007 01:35:20 -00001.71
--- src/backend/commands/proclang.c24 Jan 2007 23:50:49 -0000
***************
*** 61,74 ****
Oidfuncargtypes[1];
/*
- * Check permission
- */
- if (!superuser())
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to create procedural language")));
-
- /*
* Translate the language name and check that this language doesn't
* already exist
*/
--- 61,66 ----
***************
*** 97,102 ****
--- 89,103 ----
(errmsg("using pg_pltemplate information instead of CREATE LANGUAGE parameters")));
/*
+ * Check permission
+ */
+ if (!pltemplate->tmpltrusted && !superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create untrusted procedural language")));
+
+
+ /*
* Find or create the handler function, which we force to be in the
* pg_catalog schema. If already present, it must have the correct
* return type.
***************
*** 189,194 ****
--- 190,203 ----
errhint("The supported languages are listed in the pg_pltemplate system catalog.")));
/*
+ * Check permission
+ */
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create custom procedural language")));
+
+ /*
* Lookup the PL handler function and check that it is of the expected
* return type
*/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
| | 3 answer | Add comment |
|
| Cannot connect to one specific postgres database on a server Matt Busby 00:39:13 |
| | Hello,
I am having trouble connecting to one specific database on my server. I
can connect to the server via pgadmin and have pgadmin display 7 or 8
databases I have running on the server. I can successfully connect to
all of the databases except one. This particular database I am trying to
connect to will just cause pgadmin to hang/crash
I have been using pgadmin for over 2 years to connect to this particular
database that wont connect now. This just started happening a week ago
and is frustrating me so bad! I have installed/reinstalled/uninstalled
about every version on pgadmin with no success. I restarted postgres on
the server with no success.
My server is running linux, and I am using windows version of pgadmin.
I WOULD REALLY APPRECIATE ANY HELP!!! THANKS SO MUCH!!!
Matt Busby
|
| | 5 answers | Add comment |
|
| Re: Installing PostgreSQL under Cpanel Erick Papa 00:39:13 |
| | There are a couple of PostgreSQL tutorials around the web to make it
work with WHM.
I have followed them. Downloaded the *.rpm files and installed them.
Then gone
into my WHM (https://myserver:2087) and enabled the config, and set up
the postgres user with an "su" command "adduser postgres".
Now what?
1. Where's the interactive shell? How can I start creating a database,
creating users, testing things out?
2. How do I start the service? How should I set it up to restart
automatically if (a) the service fails (b) machine reboots?
I looked at the Documentation
(http://www.postgresql.org/docs/8.2/interactive/config-setting.html)
and could not find one intuitive instruction to actually get cracking
with PostgreSQL without getting all configgy.
I'd appreciate some pointers.
Thanks!
---------------------------(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
|
| | 27 answers | Add comment |
|
| column insert/alter got me stumped! John Smith 00:39:13 |
| | guys,
just wanna change value of 1 existing column
# insert into tablename (columnname) values ('value');
...works
# select columnname from tablename where columnname='value';
...works
# insert into tablename (columnname) values ('value') select
columnname from tablename where columnname='value';
or
# insert into tablename (columnname) values ('value') where columnname='value';
...combinations don't work
# alter table tablename alter column columnname set value='value';
...doesn't work either
embarrassingly simple? pgadmin III thinks so
jzs
http://www.postgresql.org/docs/8.1/static/sql-insert.html
http://www.postgresql.org/docs/8.0/static/sql-altertable.html
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 1 answer | Add comment |
Wednesday, 24 January 2007
|
| Example of RETURNING clause to get auto-generated keys from INSERT Ken Johanson 23:43:02 |
| | Greetings,
I am looking into possibly contributing some code for one of the
existing PG drivers, that will allow us to, after INSERT, get a
ResultSet containing the server generated keys (sequences or other).
I've been told that (short of implementing a new V4 server protocol) the
most effective way to do this, may be to use PG's RETURNING clause.
However I could really use some example queries, since I'm not
proficient enough with PG and this clause to know how to get the values.
I do know that the query should:
-support multiple values, ie. insert int tbl (a,b) values (1,2),(3,4),
should return a result with 2 rows containing the new keys (one for each
column the users declares).
-query the values atomically (so that insert by another client won't
skew the curval / sequence) (obvious but deserves mention)
-ideally be predictable - just in case the sequence doesn't use a
increment value of one, or if some other non-sequence (triggers) or
numeric (uuids) generator is used.
-ideally not require parsing the user INSERT query (for table names
etc), though I expect that (in order to use RETURNING) I will have to
append to it.
The API I'd implement this for (jdbc), does require us to declare what
columns we are interested in getting generated keys for, so that might
preclude needing resultset metadata to know which columns have server
generated keys.
So if anyone can give SQL samples of how to best make this work, I would
be very much appreciative.
Thanks,
Ken
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
|
| | 3 answer | Add comment |
|
| capacity of tables Guillermo Arias 23:39:10 |
| | Hello, i am Guillermo Arias, from Peru. I have a doubt about capacity
of tables.
I am developing a software for accountants, and my principal problem
is about the table for the vouchers. I have to decide to make a table
for each year or only one table for all the years.
This table has 11 fields: varchar(10) and 2 fields: numeric (12,2) and
is intended to have 900,000 records per year x 13 years = 11'700,000
records
What can you suggest me? i do not want the system to be slow using
this table.
thanks
guillermoariast@hotmail.com
---------------------------------------------------------------------
Get your FREE, LinuxWaves.com Email Now! --> http://www.LinuxWaves.com
Join Linux Discussions! --> ; http://Community.LinuxWaves.com
|
| | 3 answer | Add comment |
|
| Linuxworld Toronto, April 30 - May 2 Robert Bernier 23:14:34 |
| | Is anybody planning to attend this and set up a booth?
http://www.it360.ca/
Robert
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| | 5 answers | Add comment |
|
| Re: Cannot Restart PostgreSQL-8.1.4 -- SOLVED! Rich Shepard 22:38:02 |
| | On Wed, 24 Jan 2007, Tom Lane wrote:
Have you looked in the postmaster log?
Tom,
I went looking for it before writing, but did not find it. Now I have.
The ownership and permissions of /var/lib/pgsql and /var/lib/postgresql were
FUBAR.
I changed the permissions and it's now running.
Thanks,
Rich
--
Richard B. Shepard, Ph.D. | The Environmental Permitting
Applied Ecosystem Services, Inc. | Accelerator(TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
---------------------------(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
|
| | Add comment |
|
| weird buildfarm failures on arm/mipsel and --with-tcl Stefan Kaltenbrunner 22:24:20 |
| | one of my new buildfarm boxes (an Debian/Etch based ARM box) is
sometimes failing to stop the database during the regression tests:
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=quagga&dt=2007-01-08%2003:03:03
this only seems to happen sometimes and only if --with-tcl is enabled on
quagga.
lionfish (my mipsel box) is able to trigger that on every build if I
enable --with-tcl but it is nearly impossible to debug it there because
of the low amount of memory and diskspace it has. (two consecutive
failures will run the kernel out of memory due to the resources consumed
by the still running processes).
After the stopdb failure we still have those processes running:
pgbuild 3389 0.0 1.5 39632 4112 ? S 06:14 0:03
/home/pgbuild/pgbuildfarm/HEAD/inst/bin/postgres -D data
pgbuild 3391 0.0 0.9 39632 2540 ? Ss 06:14 0:00
postgres: writer process
pgbuild 3392 0.0 0.5 11220 1348 ? Ss 06:14 0:00
postgres: stats collector process
pgbuild 3488 0.0 2.4 43640 6300 ? Ss 06:15 0:01
postgres: pgbuild pl_regression [local] idle
pgbuild 3489 0.0 0.0 0 0 ? Z 06:15 0:00
[postgres] <defunct>
Any ideas on how to debug that any further ?
Stefan
---------------------------(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
|
| | 5 answers | Add comment |
|
| [pgsql-patches] pthread option of msvc build. Hiroshi Saito 22:24:05 |
| | Hi Magnus-san.
I am trying simple construction by operating config.pl.
It has changed wonderfully now. however, I do not use ecpg,
and see the simplest construction. At that time, even pthread
might not be needed. It was simple.
Please consider this.
P.S)
I can't catch up with your quick work. However, I will try the debugging
execution with VS2005 by the arrangement option. tools/msvc will
surely facilitate debugging.!
Thanks.
Regards,
Hiroshi Saito
---------------------------(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
|
| | 4 answer | Add comment |
|
| Default permissisons from schemas Stephen Frost 22:11:43 |
| | Greetings,
* Stephen Frost (sfrost@snowman.net) wrote:> It seems unlikely that I'm going to have time at the rate things are> going but I was hoping to take a whack at default permissions/ownership> by schema. Kind of a umask-type thing but for schemas instead of roles> (though I've thought about it per role and that might also solve the> particular problem we're having atm).
Following up on my reply to Joshua, what I'd like to propose is, for
comments and suggestions:
ALTER SCHEMA name [ [ WITH ] [ DEFAULT ] option [ ... ] ]
where option can be:
{ GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER | EXECUTE }
[,...] | ALL [ PRIVILEGES ] }
TO { role | PUBLIC } [, ...] [ WITH GRANT OPTION ]
} [, ...]
OWNER role
pg_namespace would be modified to have two new columns,
nspdefowner oid, and nspdefacl aclitem[]. When NULL these would have
no effect. When not-null the 'nspdefowner' would be the owner of all
objects created in the schema. When not-null the 'nspdefacl' would be
the initial acl for the object (modified for what grants are valid for
the specific type of object). These can only be changed by the schema
owner and the 'OWNER role' must have create permissions in the schema.
Ideally this would be checked when the ALTER SCHEMA is issued and then
a dependency created for that. If that's not possible today then the
rights check would be done when an object creation is attempted,
possibly with a fall-back to check the current user's rights.
The defaults would be NULL for these so there would be no change in
behaviour unless specifically asked for.
I believe this would cover the following to-do item:
Allow GRANT/REVOKE permissions to be inherited by objects based on
schema permissions
Comments?
Thanks,
Stephen
|
| | 13 answers | Add comment |
|
| ruby-postgres module needs work, maintainer gone Jeff Davis 22:03:21 |
| | 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.
Regards,
Jeff Davis
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
| | Add comment |
|
| missing cache data for cache id 27 Brian 21:42:22 |
| | I'm getting the above error when i try to replace a function of mine. It
seems i have two problems: the latest dump (through phpPGAdmin) works
fine, except that a function that should return a record was replaced
without the column definition list, so calls on it are failing.
from pg_dump:
CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype() RETURNS
SETOF record AS $$
should be:
CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name text,
OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$
So, i tried re-defining the function with the OUT params, and was hit
with the error in the subject line. I was able to DROP it first, then
re-create it. Now everything seems fine. But does anyone know what the
error means?
And why does the function definition in the db dump not reflect that OUT
params are called for?
Here's the entire function, fwiw:
-- snip --
CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name text,
OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$
DECLARE
recrecord;
BEGIN
FOR rec IN
EXECUTE 'SELECT id, name, 1 AS total FROM service_type ORDER BY NAME ASC'
LOOP
name := rec.name;
id := rec.id;
SELECT INTO rec.total SUM(CASE sp.accepted WHEN TRUE THEN 1 ELSE 0 END)
FROM service_provider AS sp WHERE sp.id IN
(
SELECT spst.service_provider_id FROM service_provider_service_type AS
spst WHERE spst.service_type_id = rec.id
);
-- If none for this service type, give it a total of zero
IF rec.total IS NULL THEN
SELECT INTO total 0;
ELSE
total := rec.total;
END IF;
RETURN NEXT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- snip --
brian
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
| | 6 answers | Add comment |
|
| About PostgreSQL certification Iannsp 21:41:42 |
| | Hello,
I did like to know what you think about the postgresql certifications
provided for
PostgreSQL CE
http://www.sraoss.co.jp/postgresql-ce/news_en.html
CertFirst
http://www.certfirst.com/postgreSql.htm
My question is about the validate of this certification for the clients.
Make difference to be certified?
thanks for advanced.
Ivo Nascimento.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 19 answers | Add comment |
|
| how to read bytea field marcelo Cortez 18:53:23 |
| | folks
help me ,i cant read bytea type field's.
how to convert bytea to text or varchar ?
when using bytea types?
any clue be appreciated
best regards
mdc
__________________________________________________
Pregunt . Respond . Descubr .
Todo lo que quer as saber, y lo que ni imaginabas,
est en Yahoo! Respuestas (Beta).
Probalo ya!
http://www.yahoo.com.ar/respuestas
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 13 answers | Add comment |
|
