Re: [HACKERS] 8.0b4: COMMIT outside of a transaction echoes
On Tue, 2004-10-26 at 21:42 -0400, Bruce Momjian wrote: test= begin; BEGIN test= commit; COMMIT test= commit; WARNING: there is no transaction in progress ROLLBACK Is there any reason ROLLBACK and not COMMIT is echoed here? Because the transaction was not committed, but rather rolled back. It's still a misleading message; in those circumstances, how about returning NO ACTION instead? -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA If a man abide not in me, he is cast forth as a branch, and is withered; and men gather them, and cast them into the fire, and they are burned. John 15:6 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Inability to cast regclass is too restrictive
On Sat, 2004-10-09 at 05:35, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: I tried to use regclass() in a plpgsql function to derive a tablename from its oid so as to build a command string, but I am unable to use the value returned because it cannot be cast to anything. Therefore I will have to use a complex query on the catalog to do the same work. Hmm? plpgsql is about as permissive as you can get on this point. Just assign the result to a variable of the desired type, and it will do it if the textual representations are at all compatible. Example: regression=# create function foo(oid) returns text as ' ... I'm on record that we should allow (explicit) casting to and from text for all types, using the types' I/O functions to implement it. But plpgsql already provides essentially that mechanism in its assignment operations. You just hafta do the explicit assignment... Thanks for the example. I was trying to do cmd = ''SELECT * FROM '' || regclass(someoid); -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Every good gift and every perfect gift is from above, and cometh down from the Father of lights, with whom is no variableness, neither shadow of turning. James 1:17 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Inability to cast regclass is too restrictive
Release 8.0.0.0beta3 I tried to use regclass() in a plpgsql function to derive a tablename from its oid so as to build a command string, but I am unable to use the value returned because it cannot be cast to anything. Therefore I will have to use a complex query on the catalog to do the same work. This seems overly restrictive. Would there be a problem in allowing regclass() to be cast to text? -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Let no man say when he is tempted, I am tempted of God; for God cannot be tempted with evil, neither tempteth he any man; But every man is tempted, when he is drawn away of his own lust, and enticed. James 1:13,14 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] NEW used in a query that is not in a rule
On Tue, 2004-09-14 at 19:34, Gaetano Mendola wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi all,
I'm bouncing on the following problem, I don't know if is a bug or if exist a
different
way to do it.
The following code is not meaningfull but it's an extract of what I'm trying to do:
CREATE TABLE foo ( field1 INTEGER );
CREATE OR REPLACE FUNCTION trigger_foo()
RETURNS TRIGGER AS'
DECLARE
~ my_field TEXT;
~ my_stat TEXT;
BEGIN
~ my_field = TG_ARGV[0];
~ my_stat = ''SELECT field1 FROM foo where field1 = NEW.'' || my_field;
My guess is that you are having this problem because you are executing a
query referring to NEW rather than using it directly.
I don't think you can refer to NEW in a command string given to
EXECUTE. You probably need to set up an IF...ELSIF...ELSE...END IF
structure to get the value to put into the command string.
~ EXECUTE my_stat;
~ RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER check_foo
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE trigger_foo('field1');
insert into foo values ( 3 );
I got the error in the subject, it's like the EXECUTE open another contest and
forget that is inside a rule.
Any idea someone ?
Regards
Gaetano Mendola
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBRzm37UpzwH2SGd4RAnObAKD4G6S6MdvaYsGxjS88sn+u2OJqagCg86ut
tsa/AXBfKtB12sCPBIwJAYc=
=G2DY
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
Oliver Elphick [EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
But without faith it is impossible to please him; for
he that cometh to God must believe that he is, and
that he is a rewarder of them that diligently seek
him.Hebrews 11:6
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] monetary bug
On Mon, 2004-08-23 at 14:11, Mike Mascari wrote: ... MONEY seems odd because it is interpreting its internal representation based upon locale and the locale is also determining its possible representation, so one database's MONEY isn't really the same type as another database's MONEY. However, Date Darwen's type model suggests that a database should have support for types like WEIGHT, LENGTH, and TEMPERATURE, although they could certainly be left for the user to define. They define possible representations and THE_ functions as the means to support multiple units (among other purposes.) For example, a LENGTH type would have the following selector functions: LENGTH LENGTH_IN_INCHES(NO_OF_INCHES RATIONAL); LENGTH LENGTH_IN_FEET(NO_OF_FEET RATIONAL); LENGTH LENGTH_IN_CM(NO_OF_CM RATIONAL); The difference between these and money is that there is a constant relationship between units; you may measure a distance in inches or centimetres, but it is always the same distance. The conversions between different units of money vary minute by minute. Furthermore the unit of money is very important, whereas the unit of length is not. You cannot meaningfully add amounts of money in different currencies, even if you convert to some base currency first. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA For God hath not appointed us to wrath, but to obtain salvation by our Lord Jesus Christ, Who died for us, that, whether we wake or sleep, we should live together with him. I Thessalonians 5:9,10 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] monetary bug
On Sun, 2004-08-22 at 20:40, Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: Tom Lane wrote: Are you aware that the monetary type is deprecated and is going to be dropped entirely pretty soon? What's taking so long? ;-) We keep hoping someone will step up to the plate and rewrite it, instead. Per previous discussion, the type really ought to be a thin layer over numeric, with most likely no operations of its own except I/O conversion. What are the parameters? Is it an SQL type? It seems to me a monetary type is a complex type consisting of currency code and amount -- but you couldn't sum mixed currencies. Or else it is limited to the currency of the locale, which doesn't seem particularly useful. I think that it should be removed altogether. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA For yourselves know perfectly that the day of the Lord so cometh as a thief in the night. For when they shall say, Peace and safety; then sudden destruction cometh upon them, as travail upon a woman with child; and they shall not escape. I Thessalonians 5:2,3 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Does psql use nested transactions?
On Wed, 2004-08-18 at 02:48, Greg Stark wrote: Simon Riggs [EMAIL PROTECTED] writes: This behaviour allows much closer mimicking of Oracle and other RDBMS's transactional behaviour begin 2cThis is my single biggest pet peeve with Postgres. When I was first starting it was the single most frustrating violation of the least surprise principle and now that I've been working with it for over a year it's the one that most continues to interfere with productive work. With Oracle I found it *extremely* useful on many occasions when doing manual updates to be able to check out the effects of my statements before committing them. It also meant I could do several updates or deletes and commit them all together. With Postgres I effectively have to work in autocommit mode. Starting over from scratch every time I make a typo is infeasible. It feels like trying to type in a C program using cat. I've done it before but it's not something I want to repeat often. It really isn't necessary to do that. If you are entering commands into psql manually, either they are so few that you can easily repeat them, using readline editing, or you can write the commands as a script in an external file, with BEGIN and END at its top and bottom, and run it with \i /path/to/file. If it fails, re-edit it with \!vi /path/to/file and repeat \i You certainly do not have to work in autocommit mode. It will be nice to have nested transactions, but this is not the compelling reason for doing it. Oliver Elphick ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] PITR - recovery to a particular transaction
The PITR docs that have just been put up say: But if you want to recover to some previous point in time (say, right before the junior DBA dropped your main transaction table), just specify the required stopping point in recovery.conf. You can specify the stop point either by date/time or by transaction ID. As of this writing only the date/time option is very usable, since there are no tools to help you identify which transaction ID to use. How about adding a logging option to put the transaction id on the log for every statement that modifies the database? Would that be a small enough change to be allowed into 8.0? -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA And not only so, but we glory in tribulations also; knowing that tribulation worketh patience; And patience, experience; and experience, hope. Romans 5:3,4 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PITR - recovery to a particular transaction
On Wed, 2004-08-04 at 19:16, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: How about adding a logging option to put the transaction id on the log for every statement that modifies the database? Would that be a small enough change to be allowed into 8.0? I think we could get away with adding transaction ID as one of the available %-items in log_line_prefix. I'm not sure how useful this really is though --- timestamps are probably more useful overall to have in your log. Why not both? You seem to be suggesting that using the id is less useful than the time, but surely it's going to be easier to say this disaster happened in transaction 123 so lets do a PITR up to 122 than to say this happened at time x so do PITR up to x - 1 second; the latter might miss several tranactions. Have I got the concepts wrong here? The direction I was expecting we'd head in is to provide WAL logfile examination tools. But that's not going to happen for 8.0, so any means of getting the transaction id is better than none. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA And not only so, but we glory in tribulations also; knowing that tribulation worketh patience; And patience, experience; and experience, hope. Romans 5:3,4 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Unicode restriction
In src/backend/utils/mb/wchar.c there is a check to exclude Unicode characters above 0x1. I can't see anything to explain this restriction, except possibly this in the release notes for 7.2: Reject invalid multibyte character sequences (Tatsuo) It does not explain why part of the Unicode character range is invalid. There is a Debian bug report from someone whose client is trying to store characters in the excluded range. What would be needed to enable support for it? -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Love is patient, love is kind. It does not envy, it does not boast, it is not proud. It is not rude, it is not self seeking, it is not easily angered, it keeps no record of wrongs. Love does not delight in evil but rejoices with the truth. It always protects, always trusts, always hopes, always perseveres. I Corinthians 13:4-7 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Escaping metacharacters
On Thu, 2004-07-15 at 23:02, DarkSamurai wrote:
Hi,
To prevent SQL injections, I try to neutralize SQL metacharacters.
ex:
Code:
function SQLString($s) {
$s = str_replace(', \\s, $s)'
$s = str_replace(\\, , $s);
return ' . $s . ';
Have you looked at the function PQescapeString() in the libpq library?
Using that would seem to be a simpler way of solving this problem.
Libraries such as Perl DBI have similar functions built in.
--
Oliver Elphick [EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
For God so loved the world, that he gave his only
begotten Son, that whosoever believeth in him should
not perish, but have everlasting life. John 3:16
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Is trust really a good default?
On Wed, 2004-07-14 at 05:08, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: ... The point of this explanation is that as Debian maintainer I would have to disable any procedures that attempt to edit these conffiles, or at least ensure that their operation is under package control and produce only the effects that I desire. Uh, is this relevant at all? There has been no suggestion that initdb should try any harder or less hard than it does now to write $PGDATA/pg_hba.conf. All that's been discussed is what it should write there. If you are going to hack on it to enforce your opinion of what it should do, then you'll be making the same hack either way. It's just that if people are going to do things to initdb to accommodate the distributions, they need to understand the constraints. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA God is faithful, by whom ye were called unto the fellowship of his Son Jesus Christ our Lord. I Corinthians 1:9 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Is trust really a good default?
On Tue, 2004-07-13 at 22:27, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I think --ident would be very helpful, and we know with OS's support ident too. If we're going to be doing sed-like substitutions on pg_hba.conf.sample, then we really really wanna discourage distros from hacking the sample file directly, because that could break the sed results. So I think it's important to provide the switch. Speaking for Debian, I should like to explain how pg_hba.conf is managed (at least at present and probably in the next stable release). The basic assumption is that a system-installed package is of universal applicability, so there is only one (official) database cluster. The configuration files in that cluster are actually symlinks to /etc/postgresql/*. The Debian packaged version of initdb is hacked to write those symlinks rather than copy the sample files. (An extra command option --debian-conffile does this, and is used by the installation script.) (A local user running initdb in his own space would get the upstream behaviour, but this is not the normal case for package installations.) The reasons for the changes are found in Debian policy: 1. All configuration files [conffiles] must be in /etc . [motivation: administrators should be able to find configuration files quickly, without having to research each package separately.] 2. No conffile may be changed by a package upgrade without the administrator's consent. A package (such as postgresql) cannot simply overwrite a conffile such as pg_hba.conf with a new version. Its new version is written in parallel (/etc/postgresql/pg_hba.conf.dpkg-new) and only overwrites the old one if the administrator consents. [motivation: system administrators should not be surprised by having their systems redefined without their consent.] The default pg_hba.conf installed by a new package installation is configured thus: local all postgres ident sameuser local all all ident sameuser hostall all 127.0.0.1 255.255.255.255 ident sameuser hostall all ::1::::::: ident sameuser hostall all :::127.0.0.1/128 ident sameuser hostall all 0.0.0.00.0.0.0 reject that is, to accept local connections authenticated by ident and reject the rest. The adminstrator is advised not to change the first line, so as to allow cron jobs to run. [motivation: to install the package with a sufficient level of security that it will not open the machine to remote exploits and to ensure that local users cannot spoof their identity to the database or change other people's data without permission. We trust the local ident server, since it is installed by the same administrator that is installing postgresql.] The point of this explanation is that as Debian maintainer I would have to disable any procedures that attempt to edit these conffiles, or at least ensure that their operation is under package control and produce only the effects that I desire. When initdb is rerun during major upgrades, it must then leave the previous configuration unchanged. Ensuring this is part of ensuring a smooth upgrade path, which is a major part of the package maintainer's job. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Let your character be free from the love of money, being content with what you have; for He Himself has said, I will never desert you, nor will I ever forsake you. Hebrews 13:5 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CVS tip problems
On Tue, 2004-06-01 at 01:33, Tom Lane wrote: First you might want to check which flavor of strerror_r() your platform has --- does it return int or char* ? I made the following change to the strerror_r call, which makes it work correctly with threading enabled: --- src/port/thread.c 23 Apr 2004 18:15:55 - 1.20 +++ src/port/thread.c 1 Jun 2004 07:18:26 - @@ -71,7 +71,8 @@ #if defined(FRONTEND) defined(ENABLE_THREAD_SAFETY) defined(HAVE_STRERROR_R) /* reentrant strerror_r is available */ /* some early standards had strerror_r returning char * */ - strerror_r(errnum, strerrbuf, buflen); + char buf[256]; + StrNCpy(strerrbuf, strerror_r(errnum, buf, 256), buflen); return strerrbuf; #else (I realise this is not sufficient for a patch to correct the problem.) -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Thou will show me the path of life; in thy presence is fullness of joy; at thy right hand there are pleasures for evermore. Psalms 16:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CVS tip problems
On Mon, 2004-05-31 at 19:55, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: 1. There are regression failures on timestamptz and horology which seem to have come about either on input or output of timestamps with fractional seconds. I believe I've fixed this. All regression tests pass now. 2. If the postmaster is not running, there is garbage in psql's error message: I can't duplicate that here. It looks to me like the probable explanation is a broken or incompatible version of strerror_r() on your machine. Does the failure go away if you build without thread-safety? Yes it does. I'll see if I can run with a debugging libc and find it. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA How precious also are thy thoughts unto me, O God! how great is the sum of them! If I should count them, they are more in number than the sand; when I awake, I am still with thee.Psalms 139: 17,18 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CVS tip problems
On Tue, 2004-06-01 at 01:33, Tom Lane wrote: First you might want to check which flavor of strerror_r() your platform has --- does it return int or char* ? The Linux man page for strerror_r() says From the definition in /usr/include/string.h, glibc 2.3.2 still has the version that returns char* -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Thou will show me the path of life; in thy presence is fullness of joy; at thy right hand there are pleasures for evermore. Psalms 16:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] CVS tip problems
CVS tip built on Debian unstable, i386, Linux 2.6.5 SMP.
gcc 3.3.3
./configure --with-openssl --with-pam --with-krb5 --with-gnu-ld
--with-python --with-perl --with-tcl --with-pgport=5342
--enable-thread-safety --enable-nls --enable-integer-datetimes
--enable-debug --enable-cassert --enable-depend
1. There are regression failures on timestamptz and horology which seem
to have come about either on input or output of timestamps with
fractional seconds. I tried various inputs and found that certain
timestamps with fractional seconds had one second added to the time.
This appears to be confined to the period from midnight at the start of
Dec 14 1901 GMT to midnight at the start of Jan 01 2000 GMT
junk=# select cast('Dec 13 15:59:59.50 1901 PST' as timestamptz);
timestamptz
1901-12-13 23:59:59.50
(1 row)
junk=# select cast('Dec 13 16:00:59.50 1901 PST' as timestamptz);
timestamptz
---
1901-12-14 00:01:00.50+00
(1 row)
junk=# select cast('Dec 13 23:59:59.50 1901 GMT' as timestamptz);
timestamptz
1901-12-13 23:59:59.50
(1 row)
junk=# select cast('Dec 14 00:00:00.50 1901 GMT' as timestamptz);
timestamptz
---
1901-12-14 00:00:01.50+00
(1 row)
I tried debugging this but got a segmentation fault and apparent stack
corruption in gdb, with the reported break point not anywhere I had set
one. I don't know what to do about that.
2. If the postmaster is not running, there is garbage in psql's error
message:
[EMAIL PROTECTED] export PGPORT=5342
[EMAIL PROTECTED] export PATH=/usr/local/pgsql/bin:$PATH
[EMAIL PROTECTED] psql junk
psql: could not connect to server: ,[EMAIL PROTECTED][EMAIL PROTECTED]
Is the server running locally and accepting
connections on Unix domain socket /tmp/.s.PGSQL.5342?
[EMAIL PROTECTED] psql -h localhost junk
psql: could not connect to server: ,[EMAIL PROTECTED][EMAIL PROTECTED]
Is the server running on host localhost and accepting
TCP/IP connections on port 5342?
3. There is a compilation warning that a constant will not fit into a
long in adt.c. There are two more files where INT64CONST() is required
but not supplied. Patch attached.
--
Oliver Elphick [EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
Do all things without murmurings and disputings;
that ye may be blameless and harmless, the sons of
God, without rebuke, in the midst of a crooked and
perverse nation, among whom ye shine as lights in the
world.Philippians 2:14,15
Index: src/backend/utils/adt/date.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/date.c,v
retrieving revision 1.97
diff -c -r1.97 date.c
*** src/backend/utils/adt/date.c 21 May 2004 05:08:01 - 1.97
--- src/backend/utils/adt/date.c 30 May 2004 21:11:56 -
***
*** 1461,1467
}
else if (result 0)
{
! days = (-result + INT64CONST(864 - 1)) / INT64CONST(864);
result += days * INT64CONST(864);
}
#else
--- 1461,1467
}
else if (result 0)
{
! days = (-result + INT64CONST(864) - 1) / INT64CONST(864);
result += days * INT64CONST(864);
}
#else
Index: src/backend/utils/adt/datetime.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v
retrieving revision 1.128
diff -c -r1.128 datetime.c
*** src/backend/utils/adt/datetime.c 21 May 2004 05:08:01 - 1.128
--- src/backend/utils/adt/datetime.c 30 May 2004 21:11:56 -
***
*** 1209,1215
tmask |= DTK_TIME_M;
#ifdef HAVE_INT64_TIMESTAMP
! dt2time((time * 864),
tm-tm_hour, tm-tm_min, tm-tm_sec, fsec);
#else
dt2time((time * 86400),
--- 1209,1215
tmask |= DTK_TIME_M;
#ifdef HAVE_INT64_TIMESTAMP
! dt2time((time * INT64CONST(864)),
tm-tm_hour, tm-tm_min, tm-tm_sec, fsec);
#else
dt2time((time * 86400),
***
*** 1960,1966
tmask |= DTK_TIME_M;
#ifdef HAVE_INT64_TIMESTAMP
! dt2time((time * 864),
tm-tm_hour, tm-tm_min, tm-tm_sec, fsec);
#else
dt2time((time * 86400),
--- 1960,1966
tmask |= DTK_TIME_M;
#ifdef HAVE_INT64_TIMESTAMP
! dt2time((time * INT64CONST(864)),
tm-tm_hour, tm-tm_min, tm-tm_sec, fsec);
#else
dt2time((time * 86400),
Index: src/interfaces/ecpg/pgtypeslib/dt_common.c
===
RCS
Re: [HACKERS] Tablespaces
On Wed, 2004-03-03 at 04:59, Tom Lane wrote: What might make sense is some sort of marker file in a tablespace directory that links back to the owning $PGDATA directory. CREATE TABLESPACE should create this, or reject if it already exists. It will not be enough for the marker to list the path of the parent $PGDATA, since that path might get changed by system administration action. The marker should contain some sort of unique string which would match the same string somewhere in $PGDATA. Then, if either tablespace or $PGDATA were moved, it would be possible to tie the two back together. It wouldn't be an issue on most normal systems, but might be of crucial importance for an ISP running numerous separate clusters. -- Oliver Elphick [EMAIL PROTECTED] LFIX Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Collaboration Tool Proposal
On Mon, 2004-03-01 at 08:24, Kaare Rasmussen wrote: http://gforge.org/ is not a hosting site, that is why you only found 4 Well that's what you get when you write messages at 2:30 AM. Should know better. But on this topic, does a site based on GForge similar to Sourceforge exist ? http://alioth.debian.org (It is due to be taken down for a few hours this week while it is moved to a new machine.) Oliver Elphick ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] anoncvs problem
Following instructions on http://developer.postgresql.org/docs/postgres/cvs.html does not currently work: $ cvs -d :pserver:[EMAIL PROTECTED]:/projects/cvsroot login Logging in to :pserver:[EMAIL PROTECTED]:2401/projects/cvsroot CVS password: cvs login: authorization failed: server anoncvs.postgresql.org rejected access to /projects/cvsroot for user anoncvs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] documentation bug?
On Mon, 2004-01-05 at 10:02, Tatsuo Ishii wrote:
I see following in the doc:
Table 9-21. Template Patterns for Date/Time Formatting
:
:
J Julian Day (days since January 1, 4712 BC)
I think 4712 should be 4713.
template1=# select to_char('1 Jan 4713BC'::date,'J');
to_char
-
38
(1 row)
template1=# select to_char('24 Nov 4714BC'::date,'J');
to_char
-
0
(1 row)
I think that 1 Jan 4713BC is by the Julian calendar, whereas SQL dates
are defined to be by the Gregorian calendar (even before it existed).
Those 38 days are presumably the difference between the two calendar
systems at that date.
Here's a little bug:
template1=# select to_char('24 Nov 0001'::date,'J');
to_char
-
1721753
(1 row)
template1=# select to_char('24 Nov '::date,'J');
to_char
-
1721388
(1 row)
There is no year 0, so this ought to give an error. It may explain why
we go back to 4713BC instead of 4712BC.
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
And thou shalt love the LORD thy God with all thine
heart, and with all thy soul, and with all thy might.
Deuteronomy 6:5
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] [GENERAL] Announce: Search PostgreSQL related resources
On Mon, 2004-01-05 at 14:49, Oleg Bartunov wrote: Hi there, I'm pleased to present pilot version of http://www.pgsql.ru - search system on postgresql related resources. Currently, we have crawled 27 sites, new resources are welcome. It has multi-languages interface (russian, english) but more languages could be added. We plan to add searchable archive of mailing lists (a'la fts.postgresql.org), russian documentation and WIKI for online documentation, tips, etc. We are welcome your feedback and comments. We need design solution, icons. Could I suggest a filter to limit results in foreign languages. For example, I am unable to benefit from pages written in Russian; I would like to exclude them from the search, but I see no means of doing that. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C And thou shalt love the LORD thy God with all thine heart, and with all thy soul, and with all thy might. Deuteronomy 6:5 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Spinlock support for linux-hppa?
On Thu, 2004-01-01 at 22:20, Tom Lane wrote: My object is to get 7.4.1 working on all the Debian architectures. I'd have been more willing to buy into that goal if you'd been working on it during the 7.4 beta test cycle. I gather from what you are saying that you couldn't, because Debian provides essentially no infrastructure for testing package portability in advance of official releases. That seems like a rather serious misjudgement on their part ... maybe you could lobby to get it corrected? It isn't as bad as that normally. If I put a package in unstable, it will (in the absence of a later upgrade) eventually make its way into a stable release. Therefore it would be inappropriate for me to put anything less than a later beta release of PostgreSQL there. This time, there was a long-standing blockage in moving from unstable to testing caused by the interaction of several fundamental packages (such as libc6 and perl) with release-critical bugs, so we were asked not to do uploads that might compound the problem by (potentially) introducing new RC bugs. Therefore all the 7.4 releases went to experimental instead. The logjam only cleared in time for me to put 7.4.1 in unstable. Unfortunately, this meant that packages didn't get built for other architectures unless someone using those architectures did a build from source, which apparently didn't happen with hppa. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Verily, verily, I say unto you, He that heareth my word, and believeth on him that sent me, hath everlasting life, and shall not come into condemnation; but is passed from death unto life. John 5:24 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Spinlock support for linux-hppa?
On Thu, 2004-01-01 at 17:58, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: On Thu, 2004-01-01 at 03:22, Oliver Elphick wrote: What is needed to provide spinlock support for linux on hppa? Possibly nothing --- can you try CVS tip? Bruce has already committed his patch to decouple CPU and OS assumptions there, and I changed the TAS code to be inline for gcc. AFAICS it should just work. I can't very easily get cvs tip built on linus-hppa, because I couldn't make a package of that except for experimental, but experimental doesn't get processed by the autobuilders. I saw the changes to s_lock.h, but I couldn't at first sight see how to alter 7.4.1 to incorporate them, because they coincided with a file reorganisation. My object is to get 7.4.1 working on all the Debian architectures. However, I only have i386 machines; for the rest I have to rely on the autobuilders, which makes the task of debugging rather long-winded and is likely to involve a series of faulty packages till I get it right. So is there any possibility of a backpatch for 7.4.1? or tell me which files in 7.4.1 should receive the tas assembler for gcc. Can I take it that your words above imply that the assembler code is the same for gcc on all architectures? Is there any way of finding out from cvs all the files affected by a patch? Using the web interface to view changes to one file, I don't see how to find out what else changed at the same time. Thanks. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The LORD shall preserve thy going out and thy coming in from this time forth, and even for evermore. Psalms 121:8 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Spinlock support for linux-hppa?
What is needed to provide spinlock support for linux on hppa? make[5]: Entering directory `/build/buildd/postgresql-7.4.1/build-tree/postgresql-7.4.1/src/backend/access/transam' ... hppa-linux-gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -pipe -I../../../../src/include -D_GNU_SOURCE -I/usr/include/tcl8.4 -I/usr/lib/R/include -c -o xlog.o xlog.c In file included from ../../../../src/include/storage/spin.h:50, from xlog.c:37: ../../../../src/include/storage/s_lock.h:543:2: #error PostgreSQL does not have native spinlock support on this platform. To continue the compilation, rerun configure using --disable-spinlocks. However, performance will be poor. Please report this to [EMAIL PROTECTED] make[5]: *** [xlog.o] Error 1 Full build log at http://buildd.debian.org/fetch.php?pkg=postgresqlver=7.4.1-1arch=hppastamp=1072828455file=logas=raw -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The LORD shall preserve thy going out and thy coming in from this time forth, and even for evermore. Psalms 121:8 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Spinlock support for linux-hppa?
On Thu, 2004-01-01 at 03:22, Oliver Elphick wrote: What is needed to provide spinlock support for linux on hppa? I should add that 7.3.4 built OK. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The LORD shall preserve thy going out and thy coming in from this time forth, and even for evermore. Psalms 121:8 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] initdb should create a warning message [was Re:
On Sun, 2003-11-30 at 23:18, Neil Conway wrote: Oliver Elphick [EMAIL PROTECTED] writes: The use of the word log in the directory name does tend to invite this error, and some have acted on it without asking first. I think initdb should put a README.IMPORTANT file in $PGDATA to say [...] If someone deletes something from $PGDATA without understanding what it is, they deserve what they get. People have a distressing tendency to want to shoot themselves in the foot; and the somewhat unfortunate naming of those files contributes to the problem. While it is satisfying to see stupidity properly rewarded, it is more neighbourly at least to attempt to protect a fool from his folly. It is also kinder to those who may be depending on him for the protection of their data. I do agree that we could stand to document the purpose of pg_clog and pg_xlog more clearly. However, this information belongs in the standard documentation, not scattered throughout $PGDATA. Then it needs to be stated very prominently. But the place to put a sign saying Dangerous cliff edge is beside the path that leads along it. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Who is like unto thee, O LORD, among the gods? who is like thee, glorious in holiness, fearful in praises, doing wonders? Exodus 15:11 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] initdb should create a warning message [was Re:
On Mon, 2003-12-01 at 16:39, Andrew Dunstan wrote: Renaming the directories is the only suggestion I've seen that makes sense. The others remind me of the warning that is now placed on coffee cup lids at fast food places: Caution, Contents May Be Hot. I agree that renaming the directories is the best solution. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Who is like unto thee, O LORD, among the gods? who is like thee, glorious in holiness, fearful in praises, doing wonders? Exodus 15:11 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] initdb should create a warning message [was Re: [ADMIN] Size on Disk]
On Wed, 2003-11-26 at 05:53, Tom Lane wrote:
Grzegorz Dostatni [EMAIL PROTECTED] writes:
Currently the datase is roughly 80 Megs. About half of
the size is stored in pg_xlog directory. I managed to
figure out that those files are transaction log files?
How can I delete them safely?
You can NOT. Don't even think about going there.
What you can do, if you intend only low-update-volume usage,
is reduce checkpoint_segments to reduce the number of WAL files
the system wants to keep around.
The use of the word log in the directory name does tend to invite this
error, and some have acted on it without asking first. I think initdb
should put a README.IMPORTANT file in $PGDATA to say,
pg_xlog and pg_clog are crucial to the preservation of your
data. They do not contain standard log files. Do not even think
about deleting them to save space; you would destroy your
database.
The cost is only one disk block per cluster, and it might deflect some
of the weaponry pointed at hapless feet...
Patch for initdb.c attached
I notice that pg_clog and pg_xlog are not mentioned in the index to the
documentation, which makes it more difficult for people to find out what
they are. I therefore also attach a doc patch to add index entries for
those two files.
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
Who shall ascend into the hill of the LORD? or who
shall stand in his holy place? He that hath clean
hands, and a pure heart...Psalms 24:3,4
Index: src/bin/initdb/initdb.c
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/initdb/initdb.c,v
retrieving revision 1.15
diff -c -r1.15 initdb.c
*** src/bin/initdb/initdb.c 29 Nov 2003 19:52:04 - 1.15
--- src/bin/initdb/initdb.c 30 Nov 2003 21:52:47 -
***
*** 179,184
--- 179,185
static int set_paths(void);
static char **replace_token(char **, char *, char *);
static void set_short_version(char *, char *);
+ static void set_warning_file(void);
static void set_null_conf(void);
static void test_buffers(void);
static void test_connections(void);
***
*** 1064,1069
--- 1065,1088
}
/*
+ * write out the warning file in the data dir; this is to try to ensure
+ * that users don't delete pg_xlog in the belief that it is just a log
+ * file
+ */
+ static void
+ set_warning_file(void)
+ {
+ FILE *warning_file;
+ char *path;
+
+ path = xmalloc(strlen(pg_data) + 20);
+ sprintf(path, %s/README.IMPORTANT, pg_data);
+ warning_file = fopen(path, PG_BINARY_W);
+ fprintf(warning_file, pg_xlog and pg_clog are crucial to the preservation of your\ndata. They do not contain standard log files. Do not even think\nabout deleting them to save space; you would destroy your\ndatabase.\n);
+ fclose(warning_file);
+ }
+
+ /*
* set up an empty config file so we can check buffers and connections
*/
static void
***
*** 2427,2432
--- 2446,2454
/* Top level PG_VERSION is checked by bootstrapper, so make it first */
set_short_version(short_version, NULL);
+
+ /* Write the warning file - a warning not to delete pg_xlog! */
+ set_warning_file();
/*
* Determine platform-specific config settings
Index: doc/src/sgml/backup.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/backup.sgml,v
retrieving revision 2.32
diff -c -r2.32 backup.sgml
*** doc/src/sgml/backup.sgml 29 Nov 2003 19:51:36 - 2.32
--- doc/src/sgml/backup.sgml 30 Nov 2003 22:22:35 -
***
*** 342,347
--- 342,350
listitem
para
+ indexterm scope=All
+ primarypg_clog/primary
+ /indexterm
If you have dug into the details of the file system layout of the data you
may be tempted to try to back up or restore only certain
individual tables or databases from their respective files or
Index: doc/src/sgml/wal.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/wal.sgml,v
retrieving revision 1.26
diff -c -r1.26 wal.sgml
*** doc/src/sgml/wal.sgml 29 Nov 2003 19:51:38 - 1.26
--- doc/src/sgml/wal.sgml 30 Nov 2003 22:22:35 -
***
*** 83,88
--- 83,92
titleFuture Benefits/title
para
+indexterm scope=All
+primarypg_clog/primary
+/indexterm
+
The UNDO operation is not implemented. This means that changes
made by aborted transactions will still occupy disk space and that
a permanent filenamepg_clog/filename file to hold
***
*** 283,288
Re: [HACKERS] Release cycle length
On Tue, 2003-11-18 at 04:36, Marc G. Fournier wrote: On Tue, 18 Nov 2003, Peter Eisentraut wrote: 0. As you say, make it known to the public. Have people test their in-development applications using a beta. and how do you propose we do that? I think this is the hard part ... other then the first beta, I post a note out to -announce and -general that the beta's have been tag'd and bundled for download ... I know Sean does up a 'devel' port for FreeBSD, but I don't believe any of the RPM/deb maintainers do anything until the final release ... I do in fact build debs of the beta and rc releases. These have gone into the experimental archive in Debian and are announced on Debian lists. I even posted an announcement to pgsql-general, on 10th October for example. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C A Song for the sabbath day. It is a good thing to give thanks unto the LORD, and to sing praises unto thy name, O most High. Psalms 92:1 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proposed structure for coexisting major versions
On Mon, 2003-10-27 at 10:05, Neil Conway wrote:
On Sun, 2003-10-26 at 17:24, Oliver Elphick wrote:
If it were possible to have two separate versions of the PostgreSQL
packages installed simultaneously, it would be simple to do database
upgrades by dumping from the old version and uploading to the new.
You'd need some mechanism to prevent concurrent modifications of the
source DB during the upgrade process, wouldn't you?
Yes. The existing Debian mechanism (upgrading with the same package
names) does it by shutting down the postmaster and restarting the old
postmaster on port 5431 while a dump is done.
An adaptation of that process will be used to do an upgrade of a
particular database cluster:
pg_version_upgrade
--
A new program which will replace postgresql-dump [a Debian-only
program].
It will be used to migrate a cluster from one major version to another.
Options:
-c {cluster} the name of the cluster
-v {version} the version to upgrade to (the default is the latest
version installed)
-p {clusterpath} the new clusterpath (default = old clusterpath)
-d {dump directory} the directory in which to put the dump of the old
cluster (default = old clusterpath parent)
-rrecover; continue upgrading from a previous failure
Procedure:
1. initdb a new cluster in {clusterpath}.new/data for
the new major version
2. start a postmaster for the new cluster on port 5430
3. stop the postmaster for the old cluster
4. set the status field in cluster_ports to upgrading
5. start a postmaster for the old cluster on port 5431
6. pg_dumpall the old cluster {clustername}.dumpall
7. load the dump in the new cluster {dbname}.upgrade 21
8. if there are no errors, stop the two postmasters, else exit and
set status to failed-upgrade
9. move the old cluster directory to {clusterpath}.old and move
{clusterpath}.new to {clusterpath}; in cluster_ports, set the
status field back to its original value
10. start the postmaster for the new cluster
11. (with administrator approval only) delete the old cluster and
the dump file
(All operations are done with the software version appropriate to the
cluster version.)
Changes to my original proposal:
1. it is not necessary to keep the major version number in
cluster_ports, since it can be read from the cluster's PG_VERSION file.
It seems sensible to avoid duplicating that datum. The pathname held in
that file will not be PGDATA but its parent, and PGDATA will always be
{clusterpath}/data.
2. the active field in cluster_ports is renamed status, with the
values active, inactive, upgrading or failed-upgrade.
The latest version of the proposal is to be found at
http://cvs.alioth.debian.org/cgi-bin/cvsweb.cgi/~checkout~/common/postgresql-client.html?rev=1.1content-type=text/htmlcvsroot=pkg-postgresql
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
Cast thy burden upon the LORD, and he shall sustain
thee; he shall never allow the righteous to fall.
Psalms 55:22
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Proposed structure for coexisting major versions
}read the appropriate settings from cluster_ports (and abort if the EUID is wrong). -A operate on all clusters for the current EUID -a (with start) operate on active clusters for the current EUID -r operate only on running clusters - those for which postmaster is running - for the current EUID -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Whosoever therefore shall be ashamed of me and of my words in this adulterous and sinful generation; of him also shall the Son of man be ashamed, when he cometh in the glory of his Father with the holy angels. Mark 8:38 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Debian bug report about multibyte in 7.3.3
On Sun, 2003-10-19 at 17:09, Tom Lane wrote: Michael Meskes [EMAIL PROTECTED] writes: Does anyone know this bug report: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=204000 The bug reporter is in error to be claiming he is running 7.3.3, because the assert() in question is at line 334 not 331 in 7.3.3. He may have 7.3.3 client libraries, but he must be talking to a server version between 7.3 and 7.3.2. This is curious because the version numbers are automatically generated by reportbug and he gives no hint that he is talking to a different machine. However I verified that the assertion is at line 334 in the source code that the package was compiled from. I believe the bug probably is the same one reported/fixed here: 2003-04-20 13:37 tgl * src/backend/regex/regcomp.c (REL7_3_STABLE): Fix char-vs-pg_wchar confusion in p_ere(), per failure report from Tom O'Dowd. This fix is not relevant to CVS tip anymore, but we should fix it in 7.3.*. which would mean that it is indeed fixed in 7.3.3. I suppose the reason Oliver never forwarded this report is he couldn't duplicate it... It's more because I was away at the time fetching my mother-in-law from Israel, so this bug never got dealt with at all :-( But I do have problems with these character set bugs, since it took a long time to find a way to see these characters correctly. I can confirm it does not happen in 7.4beta4 -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Commit thy way unto the LORD; trust also in him and he shall bring it to pass. Psalms 37:5 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Unicode upper() bug still present
There is a bug in Unicode upper() which has been present since 7.2:
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=139389
I had thought I had reported it before, but I can't find a record of it.
The attached Perl script illustrates the bug (the script needs DBI).
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
For the LORD God is a sun and shield; the LORD will
give grace and glory; no good thing will he withhold
from them that walk uprightly.Psalms 84:11
#!/usr/bin/perl -w
use DBI;
my $dsn = DBI:Pg:dbname=junk;host=localhost; # change database name
my ($user, $password) = ('olly', ''); # must be modified
my $dbh = DBI-connect($dsn, $user, $password,
{ PrintError = 1,
RaiseError = 1,
AutoCommit = 1 });
my $sth = $dbh-prepare(select upper(?));
my $test = \xc3\xb6; # lowercase o with diaeresis in utf-8, u+00f6
$sth-execute($test);
my $result = ($sth-fetchrow_array)[0];
if($result ne \xc3\x96) { # uppercase O with diaeresis, u+00d6
print Result $result is wrong\n;
}
$sth-finish;
$dbh-disconnect;
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Dreaming About Redesigning SQL
On Fri, 2003-10-17 at 22:52, Christopher Browne wrote: Nobody seems to have been prepared to explain the MV model in adequate theoretical terms as to allow the gentle readers to compare the theory behind it with the other theories out there. I'm not convinced that there was a great deal of theory behind Dick Pick's database design. It has always struck me as very pragmatic. In terms of storage, the substantial difference between MV and relational databases is that each MV file (=table) holds, or can hold, what would be the result of a join in a relational database. Wherever we use arrays in PostgreSQL, we effectively do the same thing. The advantages of MV are that it is very simple to program and to define your data. If you want another attribute (=column) you simply define a new dictionary entry with a new attribute number; data output formatting can be simply done by defining new dictionary items which present the same data in a different way. The MV characteristic makes it very fast to get (for example) a list of invoices for a particular customer, since the list of invoice numbers can be kept as part of the customer record. The disadvantages (at least of original PICK) are: there are no constraints (not even by typecasting); there can be no relational enquiries -- everything must be defined in the dictionary; the environment is utterly undisciplined -- anything can be changed at will with a simple text editor; even more than in MySQL, all data validation must be done by programming; there is no requirement for a record in a file to correspond at all to the structure defined in its dictionary; finally, the security model was laughable. The effects of this can be seen in many places whose applications are based on PICK. There is usually a mass of programs of various ages, with no certainty that all have the same view of the database structure. The database design is often very amateurish; frequently it truly is amateur, because the simplicity of programming makes it easy for users to roll their own systems, but they usually lack the necessary experience in data analysis. Security usually depends on user ignorance; in UniVerse migrations of old PICK databases, I have often seen entire directories of important data with 777 permissions, and with everyone using the same login. Good use of MV requires the imposition of disciplined programming in an environment which is profoundly hostile to such discipline. It is not really possible to guarantee data integrity. There are some advances on this in some implementations. I know UniVerse: it provides SQL and adds it on top of the existing MV structure; it also provides transactions. These features give some of the advantages of a relational database, provided that only SQL facilities are used, but I doubt if many people have used UniVerse to build SQL systems from scratch. I feel that SQL was provided more to satisfy the box tickers who compare tenders than with a serious intention of providing data integrity. Having used both SQL and MV, I would not now design in any MV implementation known to me a system whose data I valued. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Delight thyself also in the LORD; and he shall give thee the desires of thine heart. Psalms 37:4 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] db encoding
On Mon, 2003-10-06 at 19:30, Peter Eisentraut wrote:
About pg_encoding. There is currently no way to tell whether an encoding
exists. Normally you would put this kind of thing into a system table,
but doing that is a bit tricky with the encodings. I would like to see
pg_encoding go, so let's hear what information people need and give them a
direct way to access it.
I currently use pg_encoding in Debian's automatic upgrade script to
extract the existing default encoding from pg_database, thus:
$ psql -q -t -d template1 -c select encoding from pg_database where
datname = 'template1'
0
and then I use it to translate that number into an encoding name that
can be fed to initdb.
However, on looking at this, I can see that I don't need it, since I can
just as well do
$ psql -l | grep template1 | awk '{print $5}'
SQL_ASCII
so as to achieve the same result with only a single command.
Therefore, you don't need to keep pg_encoding for my (the Debian
package's) sake.
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
Blessed is the man that walketh not in the counsel of
the ungodly, nor standeth in the way of sinners, nor
sitteth in the seat of the scornful. But his delight
is in the law of the LORD; and in his law doth he
meditate day and night. Psalms 1:1,2
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] db encoding
On Mon, 2003-10-06 at 21:31, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: I currently use pg_encoding in Debian's automatic upgrade script to extract the existing default encoding from pg_database, thus: $ psql -q -t -d template1 -c select encoding from pg_database where datname = 'template1' 0 and then I use it to translate that number into an encoding name that can be fed to initdb. But you can do that with pg_encoding_to_char: So I see -- now, but I had missed its very existence, I'm afraid. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Blessed is the man that walketh not in the counsel of the ungodly, nor standeth in the way of sinners, nor sitteth in the seat of the scornful. But his delight is in the law of the LORD; and in his law doth he meditate day and night. Psalms 1:1,2 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] minor view creation weirdness
On Thu, 2003-10-02 at 08:40, Greg Stark wrote: Neil Conway [EMAIL PROTECTED] writes: Is this a bug? (using CVS code from yesterday) nconway=# create view baz (a,b) as select 'hello', 'world'; WARNING: column a has type unknown DETAIL: Proceeding with relation creation anyway. WARNING: column b has type unknown DETAIL: Proceeding with relation creation anyway. CREATE VIEW nconway=# 7.3 does the same thing actually. I don't know what that means though. junk=# \d baz View public.baz Column | Type| Modifiers +---+--- a | unknown | b | unknown | View definition: SELECT 'hello' AS a, 'world' AS b; There is no table behind the view, so there is no way for PostgreSQL to derive the column types of a and b. A quoted string (as supplied in the view definition) could be one of text, varchar, char, date, time, timestamp, cidr and so on. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Set your affection on things above, not on things on the earth. Colossians 3:2 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] initdb failure (was Re: [GENERAL] sequence's plpgsql)
On Fri, 2003-09-26 at 23:18, Bruce Momjian wrote: If you are loading from pg_dump, you have lots of copy commands, so how do you know which COPY command caused the failure. You just have the line number of _a_ copy. I would recommend using psql -e so that the sql commands are output too. Here is a documentation patch: Index: backup.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/backup.sgml,v retrieving revision 2.30 diff -c -r2.30 backup.sgml *** backup.sgml 31 Aug 2003 17:32:18 - 2.30 --- backup.sgml 27 Sep 2003 05:45:04 - *** *** 100,106 be read in by the applicationpsql/application program. The general command form to restore a dump is synopsis ! psql replaceable class=parameterdbname/replaceable lt; replaceable class=parameterinfile/replaceable /synopsis where replaceable class=parameterinfile/replaceable is what you used as replaceable class=parameteroutfile/replaceable --- 100,106 be read in by the applicationpsql/application program. The general command form to restore a dump is synopsis ! psql -f replaceable class=parameterinfile/replaceable -d replaceable class=parameterdbname/replaceable /synopsis where replaceable class=parameterinfile/replaceable is what you used as replaceable class=parameteroutfile/replaceable *** *** 112,117 --- 112,124 applicationpsql/ supports similar options to applicationpg_dump/ for controlling the database server location and the user name. See its reference page for more information. +/para + +para + With a large dump, it may be difficult to identify where any errors are + occurring. You may use the -e option to psql to print the SQL commands + as they are run, so that it is easy to see precisely which commands are + causing errors. /para para -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C My brethren, count it all joy when ye fall into various trials, Knowing that the testing of your faith produces endurance. James 1:2,3 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ECPG interface: 7.4beta3 compile failure; CVS tip
On Fri, 2003-09-19 at 07:38, Michael Meskes wrote: I included strndup because some systems didn't seem to have it. Any idea what else I could do? Okay, I could rename it and use only the renamed function as it's just used internally. What surprises me is that it compiles fine for me despite using the very same system as Oliver to compile. Perhaps you should enclose your definition in #ifndef __USE_GNU #endif src/include/port/linux.h now forces _GNU_SOURCE on, which in turn defines __USE_GNU. Do you somehow override the definition of _GNU_SOURCE? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Bring ye all the tithes into the storehouse, that there may be meat in mine house, and prove me now herewith, saith the LORD of hosts, if I will not open you the windows of heaven, and pour you out a blessing, that there shall not be room enough to receive it. Malachi 3:10 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] ECPG interface: 7.4beta3 compile failure; CVS tip compile failure
System: i386 (Athlon) - Debian GNU/Linux unstable PostgreSQL 7.4beta3 fails to compile: i386-linux-gcc -O2 -g -Wall -Wmissing-prototypes -Wmissing-declarations -pipe -fPIC -I../../../../src/interfaces/ecpg/include -I../../../../src/include/utils -I../../../../src/include -I/usr/include/tcl8.4 -I/usr/lib/R/include -g -c -o timestamp.o timestamp.c timestamp.c: In function `tm2timestamp': timestamp.c:71: error: syntax error before numeric constant timestamp.c:73: error: syntax error before long timestamp.c:76: error: syntax error before '=' token timestamp.c:76: error: syntax error before '' token make[5]: *** [timestamp.o] Error 1 make[5]: Leaving directory `/usr/src/mypackages/postgresql/postgresql-7.3.99.7.4beta3/build-tree/postgresql-7.4beta3/src/interfaces/ecpg/pgtypeslib' This error is fixed in CVS tip by Tom Lane's patch of 2 days ago. However CVS tip still fails to compile: make[4]: Entering directory `/home/olly/pgsql.cvs/pgsql/src/interfaces/ecpg/compatlib' i386-linux-gcc -O2 -g -Wall -Wmissing-prototypes -Wmissing-declarations -D_GNU_SOURCE -fpic -I../../../../src/interfaces/ecpg/include -I../../../../src/interfaces/libpq -I../../../../src/include/utils -I../../../../src/include -I/usr/include/tcl8.4 -I/usr/lib/R/include -c -o informix.o informix.c informix.c:138: error: syntax error before __extension__ informix.c:138: error: syntax error before len informix.c:138: error: syntax error before if ...etc Line 138 begins the definition of strndup(). However, strndup() is also declared in string.h, which is included by this file. If I rename this function to estrndup() (and also where it is called, further down) the compilation succeeds. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Give, and it shall be given unto you; good measure, pressed down, and shaken together, and running over, shall men pour into your lap. For by your standard of measure it will be measured to you in return. Luke 6:38 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] wish: limit number of connections per database
On Wed, 2003-09-10 at 20:22, bognár, attila wrote: Would it be possible to set the maximum number of connections to each database individually? I need this because the server will be shared between several users and I want to avoid that that somebody uses the maximum number of connections possible to the server, locking out others. I assume users shouldn't be allowed to use other users' databases? If so, why not have a separate postmaster (and a separate database cluster) for each user? Each one would connect on a different port, and each one could be separately configured. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_id and pg_encoding
On Sun, 2003-09-07 at 16:46, Bruce Momjian wrote: Andrew Dunstan wrote: Is there any reason to keep separate pg_id and pg_encoding programs, or should they be merged into a C version of initdb? AFAICS initdb is the only thing that uses them. Yes, I assume they would go away with a C version. I use both of them for the Debian packaging, to try to ensure that upgrading goes seamlessly. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C For whosoever shall call upon the name of the Lord shall be saved. Romans 10:13 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Debian packages of 7.4beta2
I have made Debian packages of PostgreSQL 7.4beta2 and uploaded them to Debian's experimental archive. The package version is 7.3.99.7.4beta2-1 (so that when 7.4's final version comes out, it will be perceived as a later package). They are built on a machine running current unstable, so they cannot be loaded on a woody machine. I don't plan to make a woody version until 7.4 is properly released. The packages may not be visible for a while because there are some new binary packages that need to be authorised by the archive maintainers. Comments on the packages will be welcome. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C And he said unto his disciples, Therefore I say unto you, Take no thought for your life, what ye shall eat; neither for the body, what ye shall put on. For life is more than meat, and the body is more than clothing. Consider the ravens, for they neither sow nor reap; they have neither storehouse nor barn; and yet God feeds them; how much better you are than the birds! Consider the lilies, how they grow; they toil not, they spin not; and yet I say unto you, that Solomon in all his glory was not arrayed like one of these. If then God so clothe the grass, which is to day in the field, and tomorrow is cast into the oven; how much more will he clothe you, O ye of little faith? And seek not what ye shall eat, or what ye shall drink, neither be ye of doubtful mind. But rather seek ye the kingdom of God; and all these things shall be added unto you. Luke 12:22-24; 27-29; 31. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] location of the configuration files
On Fri, 2003-02-14 at 12:17, Bruce Momjian wrote: If you want ps to display the data dir, you should use -D. Remember, it is mostly important for multiple postmaster, so if you are doing that, just use -D, but don't prevent single-postmaster folks from using PGDATA. Could not the ps line be rewritten to show this, as the backend's ps lines are rewritten? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C God be merciful unto us, and bless us; and cause his face to shine upon us. Psalms 67:1 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 23:06, mlw wrote: Bruce Momjian wrote: Can non-root write to /var/run? Shouldn't be able too But it should be able to write under /var/run/postgresql, which the distribution will set up with the correct permissions. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C God be merciful unto us, and bless us; and cause his face to shine upon us. Psalms 67:1 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 22:53, Bruce Momjian wrote: Oliver Elphick wrote: What your comments strongly suggest to me is that projects like PostgreSQL and pine, along with everything else, should comply with FHS; then there will be no confusion because everyone will be following the smae standards. Messes arise when people ignore standards; we have all seen the dreadful examples of MySQL and the Beast, haven't we? Can the FHS handle installing PostgreSQL as non-root? Certainly. It is only necessary to set permissions correctly in /etc/postgresql and /var/run/postgresql. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C God be merciful unto us, and bless us; and cause his face to shine upon us. Psalms 67:1 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] location of the configuration files
On Fri, 2003-02-14 at 02:49, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: I'm not entirely sure why SE Linux has a problem, seeing that postgres needs read-write access to all the files in $PGDATA, but assuming the need is verified, I could do this by moving the pid file from $PGDATA/postmaster.pid to /var/run/postgresql/5432.pid and similarly for other ports. This would also have the benefit of being more FHS compliant What do people think about that? No chance at all. Breaking the connection between the data directory and the postmaster.pid file means we don't have an interlock against starting two postmasters in the same data directory. Yes; that would take a lot of effort to get round. Not worth it, I think. I do not see the argument for moving the pid file anyway. Surely no one's going to tell us that the postmaster shouldn't have write access to the data directory? I'm waiting for a response on that one; I don't understand it either. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C God be merciful unto us, and bless us; and cause his face to shine upon us. Psalms 67:1 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
On Fri, 2003-02-14 at 02:45, [EMAIL PROTECTED] wrote: 3.7.1 Purpose /etc contains configuration files and directories that are specific to the current system. 3.7.4 Indicates that Host-specific configuration files for add-on application software packages must be installed within the directory /etc/opt/package, where package is the name of the subtree in /opt where the static data from that package is stored. 3.12 indicates: /opt is reserved for the installation of add-on application software packages. A package to be installed in /opt must locate its static files in a separate /opt/package directory tree, where package is a name that describes the software package. ... It would make most sense, based on FHS, for PostgreSQL information to assortedly reside in: - /etc/opt/postgresql or /etc/postgresql, for static config information; I feel that /opt (and therefore /etc/opt) are intended for the use of vendors; so commercial packages designed to fit in with FHS should use those. I don't think they are for locally built stuff. No matter; it illustrates the main point, which is that these things should be easily configurable. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C God be merciful unto us, and bless us; and cause his face to shine upon us. Psalms 67:1 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 21:21, Vince Vielhaber wrote: I certainly wasn't trying to provoke anything. It just seems odd to me that when the distribution installs a package and places it's config files in /etc and later the admin happens to upgrade by the instructions with the package, it's acceptable for the config files to now be in two places and you don't find it confusing. What happens when a new admin comes on and tries to figure out which config file is which? Ever try to figure out where the hell Pine's config really is? I've not used pine, and there doesn't seem to be an official Debian package, (it doesn't allow any changes to its source, I believe, which makes it ineligible). But if it were an official package, I know I should look in /etc/pine. If the admin installs a local build of something he has installed as a package, he will presumably take care to separate the two. If his local build is to replace the package, he should purge the installed package, so that there are no traces of it left. Since he is administering a distribution installation, it is certainly his responsibility to understand the difference between local and distributed packages, as well as the different places that each should put their configuration files. (Incidentally, Debian's changes from the upstream configuration are documented in the package.) In the end, though, when we package for a distribution, we expect people to use the packages. If they want to build from source, the packages system lets them do it. Anyone who is building from the upstream source must be presumed to know what he is doing and take responsibility for it. What your comments strongly suggest to me is that projects like PostgreSQL and pine, along with everything else, should comply with FHS; then there will be no confusion because everyone will be following the smae standards. Messes arise when people ignore standards; we have all seen the dreadful examples of MySQL and the Beast, haven't we? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The earth is the LORD'S, and the fullness thereof; the world, and they that dwell therein. Psalms 24:1 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 13:32, Christopher Browne wrote: Everybody has room in /etc for another 10K of data. Where you have room for something that might potentially be a half terrabyte of data, and is not infrequently several gigabytes or more, is pretty system-depenendent. Ah, but this has two notable problems: 1. It assumes that there is a location for the configuration files for /the single database instance./ If I have a second database instance, that may conflict. I think that moving configuration to [/usr/local]/etc/postgresql implies the need for sub-directories by port, possibly with a default config to be used if there is no port-specific config file. 2. It assumes I have write access to /etc If I'm a Plain Old User, as opposed to root, I may only have read-only access to /etc. The location should be configurable; I hope we're talking about the default here. For distributions it should be /etc/postgresql; for local builds it should be /usr/local/etc/postgresql, assuming you have root access. If you don't, the -c configfile switch suggested elsewhere in this debate would be needed. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The earth is the LORD'S, and the fullness thereof; the world, and they that dwell therein. Psalms 24:1 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 12:00, Vince Vielhaber wrote: Which means if the the vendor installed Postgresql (say, the Red Hat Database) you'd expect config files to be in /etc. If the postgresql is compiled from source by local admin, you might look somewhere in /usr/local. Then why not ~postgres/etc ?? Or substitute ~postgres with the db admin user you (or the distro) decided on at installation time. Gives a common location no matter who installed it or where it was installed. Because it doesn't comply with FHS. All projects should remember that they coexist with many others and should do their best to stick to common standards. The default config file location should be set as a parameter to ./configure, which should default to /usr/local/etc/postgresql. Those of us who build for distributions will change it to /etc/postgresql. I suppose if we want to run different postmasters simultaneously, we could have /etc/postgresql/5432/ and so on for each port number being used. Perhaps have a default set in /etc/postgresql/ which can be used if there is no port-specific directory, but a postmaster using those defaults would have to have PGDATA specified on the command line. On the same lines, I have just had a request (as Debian maintainer) to move the location of postmaster.pid to the /var/run hierarchy; firstly, so as to make it easier for the administrator to find, and secondly so as to make it easier to configure SE Linux policy for file access. (SE Linux is the highly secure version produced by the NSA.) I'm not entirely sure why SE Linux has a problem, seeing that postgres needs read-write access to all the files in $PGDATA, but assuming the need is verified, I could do this by moving the pid file from $PGDATA/postmaster.pid to /var/run/postgresql/5432.pid and similarly for other ports. This would also have the benefit of being more FHS compliant What do people think about that? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The earth is the LORD'S, and the fullness thereof; the world, and they that dwell therein. Psalms 24:1 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 17:52, Vince Vielhaber wrote: Seems to me that if FHS allows such a mess, it's reason enough to avoid compliance. Either that or those of you who build for distributions are making an ill advised change. Simply because the distribution makes the decision to add PostgreSQL, or some other package, to it's distribution doesn't make it a requirement to change the location of the config files. Debian (and FHS) specifically requires that. All configuration files MUST be under /etc; the reason is to make the system administrator's job easier. Part of the raison d'etre of a distribution is to rationalise the idiosyncrasies of individual projects. The locations used by locally-built packages are up to the local administrator, but they really should not be in /etc and are recommended to be under /usr/local. I really don't see why there is such a not-invented-here mentality about this issue. I say again, standards-compliance is the best way. It makes life easier for everyone if standards are followed. Don't we pride ourselves on being closer to the SQL spec than other databases? Any way, if PostgreSQL stays as it is, I will continue to have to ensure that initdb creates symlinks to /etc/postgresql/, as happens now. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The earth is the LORD'S, and the fullness thereof; the world, and they that dwell therein. Psalms 24:1 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 18:45, Bruce Momjian wrote: Now, on to this configuration discussion. Seems moving the config file out of $PGDATA requies either: 1) we specifiy both the config directory and the data directory on postmaster start 2) we specify the pgdata directory inside postgresql.conf or other config file Is this accurate? The default start would read the config file from its predefined location, set by ./configure. No command line options would be necessary for the postmaster to run, though they could be provided. The config file should contain the pgdata location; this and any other parameter should be overridden if a different location is specified by a command-line option. I think the config should be able to contain all information that can be specified on the command line (except, of course, the location of the configuration file.) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The earth is the LORD'S, and the fullness thereof; the world, and they that dwell therein. Psalms 24:1 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 18:45, Bruce Momjian wrote: Oliver Elphick wrote: On Thu, 2003-02-13 at 17:52, Vince Vielhaber wrote: Seems to me that if FHS allows such a mess, it's reason enough to avoid compliance. Either that or those of you who build for distributions are making an ill advised change. Simply because the distribution makes the decision to add PostgreSQL, or some other package, to it's distribution doesn't make it a requirement to change the location of the config files. ... I really don't see why there is such a not-invented-here mentality about this issue. I say again, standards-compliance is the best way. It makes life easier for everyone if standards are followed. Don't we pride ourselves on being closer to the SQL spec than other databases? Any way, if PostgreSQL stays as it is, I will continue to have to ensure that initdb creates symlinks to /etc/postgresql/, as happens now. It doesn't have anything to do with not-invented-here, which is a common refrain by people who don't like our decisions, like Why don't you use mmap()? Oh, it's because I thought of it and you didn't. Does anyone seriously believe that is the motiviation of anyone in this project! I certainly don't. My apologies. I withdraw the comment, which was provoked mostly by Vince's response, quoted above. I agree that it is not characteristic of the project. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The earth is the LORD'S, and the fullness thereof; the world, and they that dwell therein. Psalms 24:1 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] location of the configuration files
On Thu, 2003-02-13 at 19:30, Robert Treat wrote: If we're going to do this, I think we need to account for all of the files in the directory including PG_VERSION, postmaster.opts, Not PG_VERSION; that is intimately associated with the data itself and ought to stay in the data directory. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The earth is the LORD'S, and the fullness thereof; the world, and they that dwell therein. Psalms 24:1 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgbash-7.3 released
On Tue, 2003-02-11 at 08:46, SAKAIDA Masaaki wrote: I'm pleased to announce the release of pgbash-7.3. http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html How do the non-PostgreSQL features of pgbash relate to standard bash? Do you also keep up to date with new releases of bash? or is there no connection? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Make a joyful noise unto the LORD, all ye lands. Serve the LORD with gladness; come before his presence with singing. Know ye that the LORD he is God; it is he that hath made us, and not we ourselves; we are his people, and the sheep of his pasture. Psalms 100:1-3 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] UNIQUE not unique with inheritance (workaround?)
On Sat, 2003-02-08 at 19:34, Samuel Sieb wrote: Is there any workaround for this problem? I'm getting involved in a project where inheritance is an incredibly useful feature, but the non-unique issue could be a serious stumbling block. Is there any way to work around it with a trigger or something? Give each table in the hierarchy a foreign key reference to another table which holds a unique list of the primary keys and a column that says which table they are in. Use triggers to update this other table and to prevent duplications in the hierarchy. . -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C But the LORD is in his holy temple; let all the earth keep silence before him. Habakkuk 2:20 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Cannot break out of createuser
It is not possible to break out of createuser with ctrl-c, ctrl-\ or kill -TERM. The reason is that this line: # Don't want to leave the user blind if he breaks # during password entry. trap 'stty echo /dev/null 21' 1 2 3 15 should be: trap 'stty echo /dev/null 21; exit 1' 1 2 3 15 or even trap 'stty echo /dev/null 21' EXIT -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C But the LORD is in his holy temple; let all the earth keep silence before him. Habakkuk 2:20 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [SQL] Function for adding Money type
On Sun, 2003-01-26 at 13:53, D'Arcy J.M. Cain wrote: This year, my team is planning on improving the MONEY type. Of course, we can always make it a user defined type if PostgreSQL doesn't want it. We will at least put it into contrib. However, if people think that it is useful and want to leave it in the main tree that's good too. What we want to do is a) switch to a 64 bit integer from a 32 bit integer in order to hold amounts of any reasonabe size and b) allow it to be cast to and from more types. Perhaps we can also add the ability to specify the number of decimal places on output but I am not sure if that would affect the primary benefit of using it, speed. A money type needs to specify what currency is held. The current one changes the currency with the locale, which makes nonsense of existing data. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Wash me thoroughly from mine iniquity, and cleanse me from my sin. For I acknowledge my transgressions; and my sin is ever before me. Against thee, thee only, have I sinned, and done this evil in thy sight... Psalms 51:2-4 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Survey results from the PostgreSQL portal page
On Sun, 2003-01-19 at 14:20, Justin Clift wrote: Dave Page put up a new survey on the PostgreSQL portal page very recently, What would attract the most new PostgreSQL users? ... Other interesting conclusions can be drawn from the results too, one of which is that only about 2% of people are asking for more features, and also that only about 2% are looking for better marketing. I suspect the majority of those who responded are technical people who despise marketing. I also suspect that most people didn't answer the question asked but instead said what they themselves most wanted. But the only thing that will get many more users is much better marketing. If people don't hear about PostgreSQL, they will never even think of using it. I looked at the shelves of database books in Blackwells in Oxford yesterday: lots on Oracle and Sql Server and DB and several on MySQL. There were 2 on Postgresql, and only one copy of each. I'd be interested to know what the commercial PostgreSQL companies think about it -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The LORD is my strength and song, and he is become my salvation; he is my God, and I will prepare him an habitation; my father's God, and I will exalt him. Exodus 15:2 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.3.1 on linux
On Tue, 2003-01-14 at 20:55, John Liu wrote: createlang plpgsql template1 ERROR: stat failed on file '$libdir/plpgsql': No such file or directory createlang: language installation failed is the above error normal in 7.3.1 on linux? I find I'm getting the same. This will happen if the plpgsql.so language file is not in the directory specified by `pg_config --pkglibdir'. That directory's path is substituted for '$libdir' by the backend. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C For I know that my redeemer liveth, and that he shall stand at the latter day upon the earth Job 19:25 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Upgrading rant.
On Sat, 2003-01-04 at 02:17, Tom Lane wrote: There isn't any simple way to lock *everyone* out of the DB and still allow pg_upgrade to connect via the postmaster, and even if there were, the DBA could too easily forget to do it. I tackled this issue in the Debian upgrade scripts. I close the running postmaster and open a new postmaster using a different port, so that normal connection attempts will fail because there is no postmaster running on the normal port. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C But because of his great love for us, God, who is rich in mercy, made us alive with Christ even when we were dead in transgressions-it is by grace you have been saved.Ephesians 2:4,5 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PostgreSQL Password Cracker
On Tue, 2002-12-31 at 17:49, Bruce Momjian wrote: Tom Lane wrote: Devrim GUNDUZ [EMAIL PROTECTED] writes: Some guys from Turkey claim that they have a code to crack PostgreSQL passwords, defined in pg_hba.conf . http://www.core.gen.tr/pgcrack/ This is not a cracker, this is just a brute-force try all possible passwords search program (and a pretty simplistic one at that). I'd say all this proves is the importance of choosing a good password. Using only lowercase letters is a *bad* idea, especially if you're only going to use five of 'em... Yea, that was my reaction too. Hard to see how we can guard against this. Keep a table of usernames used in connection attempts that failed because of a bad password. After 2 such failures, add 1 second sleep for each successive failure before responding to the next attempt for the same username. Max it at say 60 seconds. That should make brute force cracking unfeasible unless someone gets very lucky or the password is particularly weak. Zero the entry for a username as soon as there is a good connection. Is it worth doing? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Ye have heard that it hath been said, Thou shalt love thy neighbour, and hate thine enemy. But I say unto you, Love your enemies, bless them that curse you, do good to them that hate you, and pray for them which despitefully use you, and persecute you; Matthew 5:43,44 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] What else needs to be done for 7.3.1?
On Thu, 2002-12-19 at 18:40, Robert Treat wrote: On Thu, 2002-12-19 at 12:58, Bruce Momjian wrote: OK, what additional things need to be done for 7.3.1? As far as I know, we have done everything. Do we want to coordinate with Lamar or Oliver about having packages ready to coincide with the release announcement? All is ready for when the new tar.gz appears. Once I have downloaded it, it should only take 10 minutes or so to package. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C And she shall bring forth a son, and thou shall call his name JESUS; for he shall save his people from their sins.Matthew 1:21 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PQnotifies() in 7.3 broken?
On Sat, 2002-12-14 at 18:59, Bruce Momjian wrote: OK, I have updated the libpq major number in 7.3.X, and updated major and minor in HEAD. Do I need to increment the other interfaces that _use_ libpq, like ecpg? I think so. I don't think so. $ ldd /usr/lib/postgresql/lib/libecpg.so libpq.so.2 = /usr/lib/libpq.so.2 (0x40019000) libc.so.6 = /lib/libc.so.6 (0x4002e000) libssl.so.0.9.6 = /usr/lib/i686/libssl.so.0.9.6 (0x40141000) libcrypto.so.0.9.6 = /usr/lib/i686/libcrypto.so.0.9.6 (0x4016e000) libkrb5.so.17 = /usr/lib/libkrb5.so.17 (0x40226000) libcrypt.so.1 = /lib/libcrypt.so.1 (0x4025c000) libresolv.so.2 = /lib/libresolv.so.2 (0x40289000) libnsl.so.1 = /lib/libnsl.so.1 (0x4029a000) /lib/ld-linux.so.2 = /lib/ld-linux.so.2 (0x8000) libdl.so.2 = /lib/libdl.so.2 (0x402ad000) libcom_err.so.1 = /usr/lib/libcom_err.so.1 (0x402b) libasn1.so.5 = /usr/lib/libasn1.so.5 (0x402b2000) libroken.so.9 = /usr/lib/libroken.so.9 (0x402d2000) libdb3.so.3 = /usr/lib/libdb3.so.3 (0x402e3000) Here libecpg will look for libpq.so.2. When 7.3.1 is released, this libecpg will be replaced by one that looks for libpq.so.3. But I think that, unless the API of libecpg changes, its version should stay the same. If you change it with libpq, you must also change it with all the other libraries it links in, like libkrb5 and libdb3. That is clearly impracticable. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C But I will hope continually, and will yet praise thee more and more. Psalms 71:14 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PQnotifies() in 7.3 broken?
On Fri, 2002-12-13 at 05:34, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK, so what do we do with 7.3.1. Increment major or minor? Major. I thought you did it already? I did only minor, which I knew was safe. Do folks realize this will require recompile of applications by 7.3 users moving to 7.3.1? That seems very drastic, and there have been very few problem reports about the NOTIFY change. If the ABI is different, they need to recompile but don't have any indication of it. This is bad. If the major number changes, they can keep the old library around for the benefits of applications that have not yet been recompiled, while newly compiled applications can use the new library So please change it. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C For thou art my hope, O Lord GOD; thou art my trust from my youth. Psalms 71:5 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PQnotifies() in 7.3 broken?
On Fri, 2002-12-13 at 19:13, Bruce Momjian wrote: OK, let me see if I understand the ramifications. If you install 7.3.1 _on_top_of 7.3, both major versions will exist, and you your old binaries will continue to work. However, if you delete the old libraries, then install, anything compiled against 7.3 will not work until it is recompiled. Yes. You will have libpq.so.3.0 in 7.3.1; and you have libpq.so.2.2 from 7.3 (and also from 7.2.x, though in fact they are different). If you have installed 7.3.1 on top of 7.3, you will have libpq.so.3 (symlinked to libpq.so.3.0) from 7.3.1, and libpq.so.2 (symlinked to libpq.so.2.2) from an earlier release. Also, any new linking against a 7.3.1 that has both major version numbers will use the newer major? Is that right? 7.3.1 will only have the new major version number; the old one will have come from 7.3 or earlier. The library chosen by the linker is the one linked to libpq.so. -- Oliver Elphick [EMAIL PROTECTED] LFIX Limited ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] pg_hba.conf parse error gives wrong line number
With this pg_hba.conf (line numbers from vi, of course): 48 # TYPE DATABASEUSERIP-ADDRESS IP-MASK METHOD 49 50 local all all ident sameuser 51 hostall 127.0.0.1127.0.0.1ident s ameuser 52 we naturally get a parse error because of the missing user column entry in line 51. But in the log we see: Dec 10 19:27:42 linda postgres[10944]: [8] LOG: parse_hba: invalid syntax in pg_hba.conf file at line 95, token ident In a more complicated file, a bogus line number is going to make debugging very tricky. I tried following this in gdb, but haven't managed to track it through the fork of the new backend. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C I beseech you therefore, brethren, by the mercies of God, that ye present your bodies a living sacrifice, holy, acceptable unto God, which is your reasonable service. Romans 12:1 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] PostgreSQL Global Development Group
On Sun, 2002-12-08 at 20:52, Vince Vielhaber wrote: Why do you say that? Because of this taken from the above quoted text: they were under constant assault from their clients to use oracle or db2 Last I looked neither Oracle or DB2 were open source, but they both just happen to be commercial and I don't see mysql mentioned. This is a reason to increase marketing effort. I know the word has pejorative overtones in our community, but it means talking about PostgreSQL so that the PHBs hear about it and therefore begin to feel comfortable about using it. If something is familiar, it feels safe. We need to make PostgreSQL familiar. That's why we need marketing. -- Oliver Elphick [EMAIL PROTECTED] LFIX Limited ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] PostgreSQL Global Development Group
On Sun, 2002-12-08 at 22:27, Vince Vielhaber wrote: On 8 Dec 2002, Oliver Elphick wrote: If something is familiar, it feels safe. We need to make PostgreSQL familiar. That's why we need marketing. Then why wasn't mysql in the list? It's familiar. To PHBs? MySQL doesn't have anything like the marketing clout of Oracle and IBM. Be thankful it isn't in the list; it would make it a hell of a lot more difficult to dislodge it. If we want people to use PostgreSQL in preference to anything else, we have to make it known. That is marketing. If we believe we have a good product we need to say so and say why and how it's better, cheaper and purer than anything else. If there's no good marketing, bad marketing will rule the world for sure. If we don't care, we can retreat into a pure technological huddle and disappear up our own navels. The rest of the world won't even notice. Such purity will eventually destroy the project because it will lose the momentum for growth through a lack of new input. You can grow or you can decline; a steady state is almost impossible to achieve. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C For I am the LORD your God; ye shall therefore sanctify yourselves, and ye shall be holy; for I am holy. Leviticus 11:44 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Debian pacakges of 7.3
Debian packages of 7.3 for i386 architecture are available in Debian's unstable archive, as those people tracking unstable will already have noticed. I will get round to producing packages for stable when all the immediate problems are fixed. There are various packaging bugs that I am working on; check the Debian bug repository before filing new bugs -- reportbug is an essential package! debconf is now implemented, so you can choose beforehand whether to try an automatic upgrade or not. libpq++, pgeasy, psqlodbc and pgperl are included in the source package and are available as binary packages. There is a more rigorous divide between library packages and development packages. pgaccess is now a separate source package. Packages for other architectures will be produced by the autobuilders as soon as I clear up any packaging bugs that are blocking them. In the meantime, people wanting packages for other aarchitectures should build from source and let me know what (if anything) goes wrong. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Go ye therefore, and teach all nations, baptizing them in the name of the Father, and of the Son, and of the Holy Ghost; Teaching them to observe all things whatsoever I have commanded you; and, lo, I am with you alway, even unto the end of the world. Amen. Matthew 28:19,20 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [ADMIN] how to alter sequence.
On Wed, 2002-12-04 at 12:29, raja kumar thatte wrote: Hai friends, I have a sequence called raj_seq with max value 3000. ... now i wanted to increase the max value of the raj_seq to 999. How to do this change? If i drop and recreate the raj_seq, then i have to recreate the table and all triggers working on that table.But it is not an acceptable solution. So with out droping raj_seq , how do I solve this problem. Unfortunately there doesn't seem to be any easy way to do this. There is no ALTER SEQUENCE command and you can't use UPDATE on a sequence. Hackers: Could this be a TODO item for 7.4? The easiest way to do this at present is probably to dump the database, edit the dump to change the sequence max_value and then recreate the database from the edited dump. I presume you used CREATE SEQUENCE in order to get such a low max_value. If it were created from a SERIAL datatype, you would also have to edit the table definition to use a pre-created sequence. There is no means of specifying a max_value using SERIAL. -- Oliver Elphick [EMAIL PROTECTED] LFIX Limited ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [ADMIN] how to alter sequence.
On Wed, 2002-12-04 at 17:33, Dustin Sallings wrote:
What's wrong with this:
dustin=# create sequence test_seq;
CREATE SEQUENCE
dustin=# select nextval('test_seq');
nextval
-
1
(1 row)
dustin=# select setval('test_seq', );
setval
(1 row)
dustin=# select nextval('test_seq');
nextval
-
1
(1 row)
It's not the issue. The original question was how to change the upper
limit of the sequence's range, not its current value.
junk=# create sequence foo_seq maxvalue 3000;
CREATE SEQUENCE
junk=# select nextval('foo_seq');
nextval
-
1
(1 row)
junk=# select setval('foo_seq', 99);
ERROR: foo_seq.setval: value 99 is out of bounds (1,3000)
--
Oliver Elphick [EMAIL PROTECTED]
LFIX Limited
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] [GENERAL] Bug with sequence
On Wed, 2002-11-20 at 21:35, Robert Treat wrote: On Wed, 2002-11-20 at 03:53, Oliver Elphick wrote: On Mon, 2002-11-18 at 15:45, Thomas Aichinger wrote: Hi, I recently installed pg 7.2.3 on my linux box and discovered that there are some problems with datatype serial and sequence. 1.) If you create a table with a datatype serial, the corrsponding sequence will be created, but if you drop the table the sequence is not dropped. This is fixed in 7.3 out of curiosity, do you know the logic that implements this fix? I have a couple of tables that use the same sequence; I'm wondering if dropping one of the tables removes the sequence or if I have to drop all tables before the sequence is removed I just tried it. I created a sequence using SERIAL when I created a table. I used the same sequence for another table by setting a column default to nextval(sequence). I deleted the first table. The sequence was deleted too, leaving the default of the second table referring to a non-existent sequence. Could this be a TODO item in 7.4, to add a dependency check when a sequence is set as the default without being created at the same time? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land. II Chronicles 7:14 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] mislaid reference to update script for after 7.3 upgrade
I'm pretty sure I saw a reference within the last 3 or 4 weeks on one of the mailing lists to a script that would put in place, after an upgrade to 7.3, dependency information that would have been automatically created if the schema had been created ab initio in 7.3. However, I can't find it in a mailing list search. Can anyone give me a URL for that, or have I sdreamed it? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C To show forth thy lovingkindness in the morning, and thy faithfulness every night. Psalms 92:2 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG functions in Java: maybe use gcj?
On Thu, 2002-10-31 at 18:27, Barry Lind wrote: However in the proposal here we are talking about requiring a specific jvm (gcj) and actually linking parts of it into postgres. To the extent that GPL code is linked in the GPL extends to the entire code base. As I said previously there are ways to work around this, but it becomes tricky. Especially when a commercial product wants to bundle postgres and pljava. That resulting bundle is probably entirely under the GPL and then any changes to it are also GPL. So it could be the case that this company would be prevented from submitting improvements they made back to the core product because their improvements are GPLed as a result of pljava. Nothing that company does can affect the licensing of PostgreSQL itself - it doesn't belong to them, so they cannot change its licence. Nothing in the GPL forces them to put GPL copyright on their own alterations. What they cannot do is to _distribute_ binary code that links to GPL code while giving fewer rights to their distributees than they themselves received with the GPL code, whether in respect of their own code or the GPL code. Therefore they would be required to make their source changes available to anyone to whom they gave a binary, and they would not be able to restrict the further distribution of those changes. They can contribute those changes to the project under whatever licence they wish that is acceptable to the project. Furthermore, gcj is part of the GNU compiler collection, like gcc, and using it does not in itself cause code compiled under it to be subject to the GPL. Linking to its runtime library would normally cause that, but the gcj-3.0 copyright contains the following text: The libgcj library is licensed under the terms of the GNU General Public License, with this special exception: As a special exception, if you link this library with other files to produce an executable, this library does not by itself cause the resulting executable to be covered by the GNU General Public License. This exception does not however invalidate any other reasons why the executable file might be covered by the GNU General Public License. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C But they that wait upon the LORD shall renew their strength; they shall mount up with wings as eagles; they shall run, and not be weary; and they shall walk, and not faint.Isaiah 40:31 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] 7.3b3 Regression tests passed on i386 Debian
Debian GNU/Linux unstable version, build from source on i386 SMP (dual Athlon MP): all regression tests passed. Change to source required: add CFLAGS += -D_GNU_SOURCE in src/pl/plperl/GNUMakefile. ./configure --enable-recode --with-pgport=5678 --with-tcl --with-perl --with-python --with-pam --with-openssl --with-gnu-ld --with-tclconfig=/usr/lib/tcl8.3 --with-tkconfig=/usr/lib/tk8.3 --with-includes=/usr/include/tcl8.3 -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Every good gift and every perfect gift is from above, and cometh down from the Father of lights, with whom is no variableness, neither shadow of turning. James 1:17 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [SQL] [GENERAL] CURRENT_TIMESTAMP
On Fri, 2002-10-04 at 01:41, Bruce Momjian wrote: Well, let's see what others say. If no one is excited about the change, we can just document its current behavior. Oh, I see it is already documented in func.sgml: It is quite important to realize that functionCURRENT_TIMESTAMP/function and related functions all return the time as of the start of the current transaction; their values do not increment while a transaction is running. But functiontimeofday()/function returns the actual current time. Seems that isn't helping enough to reduce the number of people who are surprised by our behavior. I don't think anyone would be surprised by statement time. What do others think? I would prefer that CURRENT_TIME[STAMP] always produce the same time within a transaction. If it is changed, it will certainly break one of my applications, which explicitly depends on the current behaviour. If you change it, please provide an alternative way of doing the same thing. I can see that the current behaviour might give surprising results in a long running transaction. Surprise could be reduced by giving the time of first use within the transaction rather than the start of the transaction. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C For the word of God is quick, and powerful, and sharper than any twoedged sword, piercing even to the dividing asunder of soul and spirit, and of the joints and marrow, and is a discerner of the thoughts and intents of the heart.Hebrews 4:12 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump problems in upgrading
On Sat, 2002-09-21 at 19:49, Tom Lane wrote: 3. A view is being created before one of the tables it refers to. On thinking about it, I'm having a hard time seeing how that case could arise, unless the source database was old enough to have wrapped around its OID counter. I'd be interested to see the details of your case. While the only long-term solution is proper dependency tracking in pg_dump, there might be some shorter-term hack that we should apply... While I don't think that the oids have wrapped round, the oid of the table in question is larger than the oid of the view. It is quite likely that the table was dropped and recreated after the view was created. In fact, the view no longer works: ERROR: Relation sales_forecast with OID 26246751 no longer exists so that must be what happened. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Charge them that are rich in this world, that they not be highminded nor trust in uncertain riches, but in the living God, who giveth us richly all things to enjoy; That they do good, that they be rich in good works, ready to distribute, willing to communicate; Laying up in store for themselves a good foundation against the time to come, that they may lay hold on eternal life. I Timothy 6:17-19 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] generating postgres core files on debian
On Thu, 2002-09-19 at 11:18, Louis-David Mitterrand wrote: Hello, I am trying to debug a problem involving DBD::PgSPI that crashes the backend. It used to work fine util we installed perl-5.8. How can I get a core file of a crashed backend on a debian-linux (unstable) machine? My /etc/security/limits.conf is empty. When I login as root ulimit -c shows a limit of 0. If I set the limit to unlimited and logout/login the limit is back to 0. I think /etc/security/limits.conf is used to limit what you can set with ulimit rather than dictate the settings. You probably need to put ulimit -c unlimited in ~postgres/.bash_profile. Is it sufficient to set the proper limit and then restart postgres in the same shell to obtain core files in case the backend crashes? Yes. The core file produced by postmaster from the binary package will not be very useful to you, because the binary is stripped. You need to build the package from source and use the binary from the source tree (.../src/backend/postmaster/postmaster), not the one copied into the package tree (.../debian/usr/lib/postgresql/bin/postmaster) since the stripping is done on the package tree after the binaries are installed there. To build the package: cd /usr/local/src apt-get source postgresql # installs in postgresql-7.2.2 apt-get build-dep postgresql # build dependencies apt-get install devscripts fakeroot # needed for building anything cd postgresql-7.2.2 debuild -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Bring ye all the tithes into the storehouse, that there may be meat in mine house, and prove me now herewith, saith the LORD of hosts, if I will not open you the windows of heaven, and pour you out a blessing, that there shall not be room enough to receive it. Malachi 3:10 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RPMS for 7.3 beta.
On Wed, 2002-09-18 at 05:02, Bruce Momjian wrote: Oliver Elphick wrote: I'm unhappy because I know that I will get bug reports that I will have to deal with. They will take time and effort and would not be necessary if we had a seamless upgrade path. This last line gave me a chuckle. It is like software wouldn't be necessary if computers could read people's minds. :-) Not really! We know what the formats are before and after. We want PostgreSQL to be the best database. Why on earth can we not have the same ambition for the upgrade process? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Give, and it shall be given unto you; good measure, pressed down, and shaken together, and running over, shall men pour into your lap. For by your standard of measure it will be measured to in return. Luke 6:38 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] strip a character from text
On Wed, 2002-09-18 at 11:18, [EMAIL PROTECTED] wrote: Greetings, Does anyone know a function that strips ANY occurence of a given character from a TEXT? It sounds like a job for a PL/Perl function. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Give, and it shall be given unto you; good measure, pressed down, and shaken together, and running over, shall men pour into your lap. For by your standard of measure it will be measured to in return. Luke 6:38 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RPMS for 7.3 beta.
On Tue, 2002-09-17 at 21:40, Tom Lane wrote: In short, I'm not sure why you and Oliver are so unhappy. We may not have made the world better than before for upgrade scenarios, but I don't think we've made it worse either. I'm unhappy because I know that I will get bug reports that I will have to deal with. They will take time and effort and would not be necessary if we had a seamless upgrade path. The more PostgreSQL gets used, the more it will be used by 'clueless' users; they just install binary packages and expect them to work. That may currently be an unrealistic expectation, but I would like it to become a goal of the project. It has always been my goal as Debian maintainer, but I don't think I can achieve it for this release. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Give, and it shall be given unto you; good measure, pressed down, and shaken together, and running over, shall men pour into your lap. For by your standard of measure it will be measured to in return. Luke 6:38 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] RPMS for 7.3 beta.
On Wed, 2002-09-18 at 04:22, Bruce Momjian wrote: In summary, doing any kind of data changes is quite involved (smaller tuple header for 7.3) and because it has to be redone for every release, it is quite a pain. Is it feasible to make a utility to rewrite each table, shortening the headers and making any other necessary changes? (Taking for granted that the database has been vacuumed and the postmaster shut down.) This could build up over successive releases, with an input section appropriate to each older version and an output section for the current version. Then an upgrade from any older version to the current one could be done by pg_upgrade. Is this even worth considering? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Give, and it shall be given unto you; good measure, pressed down, and shaken together, and running over, shall men pour into your lap. For by your standard of measure it will be measured to in return. Luke 6:38 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_dump problems in upgrading
On Thu, 2002-09-12 at 00:52, Philip Warner wrote: At 12:31 PM 9/09/2002 +0100, Oliver Elphick wrote: 3. A view is being created before one of the tables it refers to. Should not views be created only at the very end? This would be trivial (and we already put several items at the end), but I am not sure it would fix the problem since views can also be on other views. I presume the bad ordering happened as a result of a drop/create on a table? Or is there some other cause? It could be, but I don't know for sure. This is a development db which quite often gets reloaded entirely and repopulated. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Let the wicked forsake his way, and the unrighteous man his thoughts; and let him return unto the LORD, and He will have mercy upon him; and to our God, for he will abundantly pardon. Isaiah 55:7 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OPAQUE and 7.2-7.3 upgrade
On Thu, 2002-09-12 at 15:31, Tom Lane wrote: Does anyone see a cleaner answer than re-allowing OPAQUE for PL handlers? Can't you just special case the language handlers when dumping 7.3 and change 'RETURNS opaque' to 'RETURNS language_handler'? That's all that is needed to let them be restored OK into 7.3. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Let the wicked forsake his way, and the unrighteous man his thoughts; and let him return unto the LORD, and He will have mercy upon him; and to our God, for he will abundantly pardon. Isaiah 55:7 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS]
On Wed, 2002-09-11 at 05:20, Tom Lane wrote:
Lamar Owen [EMAIL PROTECTED] writes:
On Tuesday 10 September 2002 11:43 pm, Tom Lane wrote:
AFAIK, we did what we could on that front in 7.2.1. If you have ideas
on how we can retroactively make things better, I'm all ears ...
So this release is going to be the royal pain release to upgrade to?
pg_dumpall from a 7.2 db, and reload into 7.2, is broken if you have
mixed-case DB names. AFAIK it's okay if you use a later-than-7.2
pg_dumpall, or reload with a later-than-7.2 psql. If Oliver's got
info to the contrary then he'd better be more specific about what
he thinks should be fixed for 7.3. Griping about the fact that 7.2.0
is broken is spectacularly unproductive at this point.
I ran pg_dumpall from 7.3 on the 7.2 database. So I am talking about
the pg_dump that is now being beta-tested. Because of the major changes
in 7.3, the 7.2 dump is not very useful. I am *not* complaining about
7.2's pg_dump!
Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:
1. The language handlers were dumped as opaque; that needs to be
changed to language_handler.
2. The dump produced:
CREATE TABLE cust_alloc_history (
...
year integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column year is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression
3. A view was created before one of the tables to which it referred.
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me.
Galatians 2:20
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS]
On Wed, 2002-09-11 at 14:59, Tom Lane wrote:
Oliver Elphick [EMAIL PROTECTED] writes:
Let me reiterate. I got these problems dumping 7.2 data with 7.3's
pg_dumpall:
1. The language handlers were dumped as opaque; that needs to be
changed to language_handler.
Okay, we need to do something about that, though I'm not sure I see
a clean solution offhand.
In 7.2, this will identify the functions that need to be dumped as
language handlers:
junk=# SELECT p.proname
junk-# FROM pg_proc AS p, pg_language AS l
junk-# WHERE l.lanplcallfoid = p.oid AND l.lanplcallfoid != 0;
proname
--
plperl_call_handler
plpgsql_call_handler
pltcl_call_handler
(3 rows)
2. The dump produced:
CREATE TABLE cust_alloc_history (
...
year integer DEFAULT date_part('year'::text,
('now'::text)::timestamp(6) with time zone) NOT NULL,
...
ERROR: Column year is of type integer but default expression is
of type double precision
You will need to rewrite or cast the expression
Hmm ... what was the original coding of the default?
year INTEGER DEFAULT date_part('year',CURRENT_TIMESTAMP)
3. A view was created before one of the tables to which it referred.
This has been a problem all along and will continue to be a problem
for awhile longer. Sorry.
Is it not enough to defer all views until the end? Why would they be
needed any sooner?
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
I am crucified with Christ; nevertheless I live; yet
not I, but Christ liveth in me; and the life which I
now live in the flesh I live by the faith of the Son
of God, who loved me, and gave himself for me.
Galatians 2:20
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS]
On Wed, 2002-09-11 at 08:20, Dave Page wrote: -Original Message- From: Oliver Elphick [mailto:[EMAIL PROTECTED]] Sent: 11 September 2002 07:29 To: Tom Lane Cc: Lamar Owen; Bruce Momjian; Philip Warner; Laurette Cisneros; [EMAIL PROTECTED] Subject: Re: [HACKERS] Let me reiterate. I got these problems dumping 7.2 data with 7.3's pg_dumpall: I wonder how many people would do something more like: pg_dumpall db.sql make install psql -e template1 db.sql rather than manually installing pg_dumpall from 7.3 first? I suppose that what people will do unless told otherwise, but the introduction of schemas means that it is much better to use 7.3's dump, otherwise, for example, all functions will be private rather than public. Perhaps a note should be added to INSTALL. At the moment it says: 2. To dump your database installation, type: pg_dumpall outputfile ... Make sure that you use the pg_dumpall command from the version you are currently running. 7.2's pg_dumpall should not be used on older databases. But now we should be telling people to use 7.3's pg_dumpall, at least for 7.2 data. (How far back can it go?) Make sure you use pg_dumpall from the new 7.3 software to dump your data from 7.2. To do this, you must have the 7.2 postmaster running and run the 7.3 pg_dumpall by using its full pathname. 7.2's pg_dumpall is unsuitable because of the introduction of schemas in 7.3 which make it necessary to grant public access to features that will, if created from a 7.2 dump, be given access by their owner only. (Have I got that right?) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me. Galatians 2:20 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] - pg_dump issues
On Wed, 2002-09-11 at 21:19, Tom Lane wrote: In the meantime, I think that we shouldn't mess with pg_dump's basically OID-order-driven dump ordering. It works in normal cases, and adding arbitrary rules to it to fix one corner case is likely to accomplish little except breaking other corner cases. I can see that Lamar and I are going to have major problems dealing with users who fall over these problems. There are some things that simply cannot be handled automatically, such as user-written functions that return opaque. Then there are issues of ordering; and finally the fact that we need to use the new pg_dump with the old binaries to get a useful dump. It seems to me that I shall have to make the new package such that it can exist alongside the old one for a time, or else possibly separate 7.3 pg_dump and pg_dumpall into a separate package. It is going to be a total pain! -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me. Galatians 2:20 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS]
On Wed, 2002-09-11 at 22:27, Bruce Momjian wrote: Dave Page wrote: Oh, I thought it was just the permissions that were the problem. Can we give them a sed script? I guess so. It seems to me that upgrading to 7.3 is going to be the stuff of nightmares, so my first thought is to try to avoid getting people to run a 7.3 utility on their 7.x database. It would be nice to see such a script run on old version dump files - but what else will break? Oliver has found a couple of things, and I wouldn't be surprised if my main installation falls over as well. If I get a chance I'll try it tomorrow. Why can't we do the remapping in the SQL grammar and remove the remapping in 7.4? Surely you will have to leave the remapping in for the benefit of anyone who jumps from = 7.2 to = 7.4 -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C I am crucified with Christ; nevertheless I live; yet not I, but Christ liveth in me; and the life which I now live in the flesh I live by the faith of the Son of God, who loved me, and gave himself for me. Galatians 2:20 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Script to compute random page cost
On Mon, 2002-09-09 at 07:13, Bruce Momjian wrote: OK, turns out that the loop for sequential scan ran fewer times and was skewing the numbers. I have a new version at: ftp://candle.pha.pa.us/pub/postgresql/randcost Latest version: olly@linda$ random test: 14 sequential test: 11 null timing test: 9 random_page_cost = 2.50 olly@linda$ for a in 1 2 3 4 5 do ~/randcost done Collecting sizing information ... random test: 11 sequential test: 11 null timing test: 9 random_page_cost = 1.00 random test: 11 sequential test: 10 null timing test: 9 random_page_cost = 2.00 random test: 11 sequential test: 11 null timing test: 9 random_page_cost = 1.00 random test: 11 sequential test: 10 null timing test: 9 random_page_cost = 2.00 random test: 10 sequential test: 10 null timing test: 10 Sequential time equals null time. Increase TESTCYCLES and rerun. Available memory (512M) exceeds the total database size, so sequential and random are almost the same for the second and subsequent runs. Since, in production, I would hope to have all active tables permanently in RAM, would there be a case for my using a page cost of 1 on the assumption that no disk reads would be needed? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS]
On Tue, 2002-09-10 at 00:50, Philip Warner wrote: ALTERNATIVELY, define the language in template1, then just edit dump1.lis to remove the line for the language definition, and run pg_restore -L dump1.lis. That doesn't work for a dump and reload, because 7.3's pg_dumpall writes a script to create the databases from template0 rather than template1. The 7.3 documentation for pg_dump says: Notes If your installation has any local additions to the template1 database, be careful to restore the output of pg_dump into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database without any local additions, copy from template0 not template1, for example: CREATE DATABASE foo WITH TEMPLATE = template0; but this seems to be out of date. pg_dumpall actually uses template0 itself. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS]
On Tue, 2002-09-10 at 18:38, Bruce Momjian wrote: I am confused. This wording seems fine to me. The confusion was mine. Of course, pg_dump doesn't create the database. I was mixing it up with pg_dumpall. However, there is a problem in that recent changes have made it quite likely that an upgrade will fail and will requre the dump script to be edited. There are some issues in pg_dump / pg_dumpall that need addressing before final release. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS]
On Tue, 2002-09-10 at 23:09, Bruce Momjian wrote: Oliver Elphick wrote: edited. There are some issues in pg_dump / pg_dumpall that need addressing before final release. OK, can you specifically list them? Message yesterday to pgsql-hackers Subject: [HACKERS] pg_dump problems in upgrading Date: 09 Sep 2002 12:31:39 +0100 Message-Id: 1031571099.24419.199.camel@linda -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded. James 4:8 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] pg_dump problems in upgrading
I am trying to populate a 7.3 database from a 7.2 dump. I used 7.3's
pg_dumpall, but this did not handle all the issues:
1. The language dumping needs to be improved:
CREATE FUNCTION plperl_call_handler () RETURNS opaque
^^
AS '/usr/local/pgsql/lib/plperl.so', 'plperl_call_handler'
LANGUAGE C;
CREATE FUNCTION
GRANT ALL ON FUNCTION plperl_call_handler () TO PUBLIC;
GRANT
REVOKE ALL ON FUNCTION plperl_call_handler () FROM postgres;
REVOKE
CREATE TRUSTED PROCEDURAL LANGUAGE plperl HANDLER plperl_call_handler;
ERROR: function plperl_call_handler() does not return type language_handler
2. Either casts or extra default conversions may be needed:
CREATE TABLE cust_alloc_history (
customer character varying(8) NOT NULL,
product character varying(10) NOT NULL,
year integer DEFAULT date_part('year'::text, ('now'::text)::timestamp(6)
with time zone) NOT NULL,
jan integer DEFAULT 0 NOT NULL,
feb integer DEFAULT 0 NOT NULL,
mar integer DEFAULT 0 NOT NULL,
apr integer DEFAULT 0 NOT NULL,
may integer DEFAULT 0 NOT NULL,
jun integer DEFAULT 0 NOT NULL,
jul integer DEFAULT 0 NOT NULL,
aug integer DEFAULT 0 NOT NULL,
sep integer DEFAULT 0 NOT NULL,
oct integer DEFAULT 0 NOT NULL,
nov integer DEFAULT 0 NOT NULL,
dbr integer DEFAULT 0 NOT NULL,
CONSTRAINT c_a_h_year CHECK (((float8(year) = date_part('year'::text,
('now'::text)::timestamp(6) with time zone)) AND (year 1997)))
);
ERROR: Column year is of type integer but default expression is of type double
precision
You will need to rewrite or cast the expression
3. A view is being created before one of the tables it refers to.
Should not views be created only at the very end?
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
Submit yourselves therefore to God. Resist the devil,
and he will flee from you.James 4:7
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Script to compute random page cost
On Mon, 2002-09-09 at 07:13, Bruce Momjian wrote: OK, turns out that the loop for sequential scan ran fewer times and was skewing the numbers. I have a new version at: ftp://candle.pha.pa.us/pub/postgresql/randcost I get _much_ lower numbers now for random_page_cost. --- Five successive runs: random_page_cost = 0.947368 random_page_cost = 0.894737 random_page_cost = 0.947368 random_page_cost = 0.894737 random_page_cost = 0.894737 linux 2.4.18 SMP dual Athlon MP 1900+ 512Mb RAM SCSI -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Submit yourselves therefore to God. Resist the devil, and he will flee from you.James 4:7 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS]
On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote: I am trying move my development database to 7.3b1. However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get the following error: pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp pg_restore: [archiver (db)] could not execute query: ERROR: function plpgsql_call_handler() does not return type language_handler Any ideas? At the moment, you have to edit the dump. Where the language handler function is declared, change RETURNS opaque to RETURNS language_handler. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Submit yourselves therefore to God. Resist the devil, and he will flee from you.James 4:7 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Inheritance
On Fri, 2002-09-06 at 19:00, elein wrote: There was a comment earlier that was not really addressed. What can you do with table inheritance that you can not do with a relational implementation? Or what would work *better* as inheritance? (you define better) There is nothing that you cannot do in some way; that way may not be very convenient compared to the use of inheritance. I consider simplicity to be preferable to conceptual purity. This is a genuine question, not a snarky comment. I really want to know. This is the reason I can think of to use inheritance: Several tables have a common set of attributes and there is some reason for these tables to be separate AND there is some reason for the common columns to be queried en masse. What kinds of some reasons are there, though? And if my condition for using table inheritance is lacking or misguided, what should be the criteria for using table inheritance? I use it when a group of tables are closely related; they are all members of some higher class. For example: person ... address | +--+--+ | | organisation individual .. pay_tax | | +++ +-+-+ ||| | | | customer supplier ...etc... staff homeworker ...etc... | ++-+ | | home_customerexport_customer It is convenient to use a higher class when you are interested in all its members and only in the attributes of the higher class. So I can say SELECT * FROM person,address WHERE address.person = person.id AND address.town = 'London'; to get all rows for people in London. I will only get those attributes that are in person itself; if I want to know about credit limits, that is only relevant in the customer hierarchy and I have to SELECT from customer instead.. Similarly, I can use the whole customer hierarchy when changing or reporting on outstanding customer balances. If foreign key relations were valid against an inheritance tree, I could implement it for a table of addresses referencing the highest level (every person has an address) and of pay and tax records at the individual level. These don't change as you go down the hierarchy, but a purely relational implementation has to be redone at each level. A reciprocal relation requires an extra table to hold all the hierarchy's keys and that in turn needs triggers to keep that table maintained. (I.e., person should have a FK reference to address and address to person; instead, address needs a reference to person_keys, which I have to create because FK against a hierarchy isn't valid.) The lack of inherited RI makes the design more complex and more difficult to understand. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C For whosoever shall call upon the name of the Lord shall be saved. Romans 10:13 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
