(Moved to -hackers)
Log Message:
---
Tablespaces. Alternate database locations are dead, long live tablespaces.
Sweet :)
There are various things left to do: contrib dbsize and oid2name modules
need work, and so does the documentation. Also someone should think about
COMMENT ON
Well, it has the same issues as COMMENT ON DATABASE, which we support,
though crudely.
Perhaps we should think about creating a shared version of
pg_description so we could have more reasonable support for comments
on shared objects. I'm not in a hurry for this but it would be a
reasonable TODO
ERROR: tablespace 292909 has been deleted
How about schema default tablespace 292909 has been deleted?
Chris
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL
I think this is wrong, primarily because it's gonna be seriously
incompatible with existing dump files. The existing technique is
that each TOC entry says who owns the object. You should use that
information and not have to rely on new additions to the file format.
This is why GRANT/REVOKE has
There is one other consideration, and that is that current pg_dump likes
to set session_auth to user of object before outputting drop command,
when '-c' is specificed.
I propose that we eliminate that set session_auth as well. If the user
running the script is the owner of that object or a
In line with my idea of keeping the hackers up to date with stuff in the
IRC channel, here are the topics of the week:
Ah yes, I forgot to add:
* pg_dumpall -Fc option comes up occasionally
Chris
---(end of broadcast)---
TIP 8: explain analyze is
Would it help to have two lines in the config file for each setting, one
with the default (comment) and one with the actual setting? So for
example, the postgresql.conf would ship with something like this:
#tcpip_socket = false #default
tcpip_socket = false
Even better, have a
I think this is wrong, primarily because it's gonna be seriously
incompatible with existing dump files. The existing technique is
that each TOC entry says who owns the object. You should use that
information and not have to rely on new additions to the file format.
Hrm. OK, i might be able to
I worded that badly. I meant allow a user to change the owner of
something to what it already is. ie. Just make the no-op allowed by
everyone. session_auth already does this.
Ah. Okay, no objection to that. (In fact I believe we put in the
special case for session_auth for exactly the same
- How does the above point affect full dumps that include schema and
data? In my proposal, the copy commands will run as the user running
the script, not the table owner anymore. Presumably, the user running
the script is a superuser. Given that it is possible for a table owner
to revoke
In line with my idea of keeping the hackers up to date with stuff in the
IRC channel, here are the topics of the week:
* We have a request for granting on all tables every other day (already
in TODO)
* We have a request for how to change database encoding every other day
(i suggest a warning
That might change the precedence of the operator and get you in a big
mess with stored expressions everywhere.
What if you could only do it on non-system operators?
Chris
---(end of broadcast)---
TIP 2: you can get off all lists at once with the
I'd prefer it if OWNER TO were only added if it is actually necessary
(or there be some option to turn it off). I don't want to edit the
entire dump file if I want to restore the database into another SQL
database.
There is the existing --no-owner option, which this patch respects, same
as
Well, the advantage of SET SESSION AUTHORIZATION is that it is SQL
compliant, whereas ALTER OWNER is not. So I'm in favor of changing
nothing.
That, however is a highly theoretical, and quite non-practical
solution. It leaves many of the world's postgresql database
non-upgradable and fixing
The proposal is to remove the comments from postgresql.conf (like
Apache) so all entries will be active. The downside is that it will not
be possible to determine which values were modified from their defaults.
One thing that truly annoys me about postgresql.conf is say I unhash an
option and
That's a fair point, but you have to admit that it's a bit abstract
while Chris has a real problem he needs to solve. Our dumps are awfully
low on the SQL-compliance scale anyway :-(
We could keep around an option for dumping the auth statements instead
of alter statements perhaps.
Sure, but
- How does the above point affect full dumps that include schema and
data? In my proposal, the copy commands will run as the user running
the script, not the table owner anymore. Presumably, the user running
the script is a superuser. Given that it is possible for a table owner
to revoke
Now that cast functions are selected through pg_cast, this should be a
fairly straightforward change. Does anyone have a problem with it?
I'm not sure the functionality is actually useful for anything except
this one issue, but arguably it's a general-purpose mechanism...
Does that help with
Not any better than it does now, no ... but AFAIK simple cases work okay
on that. What's your gripe exactly?
No gripe - was just pointing out a situation that might be improved
slightly be carrying around typmod info.
Chris
---(end of
Hi,
This is a preview patch - DON'T COMMIT IT TO HEAD!
What I've done in this patch is add the following:
ALTER AGGREGATE / OWNER TO
ALTER CONVERSION / OWNER TO
ALTER FUNCTION / OWNER TO
ALTER OPERATOR / OWNER TO
ALTER OPERATOR CLASS / OWNER TO
ALTER SCHEMA / OWNER TO
ALTER TYPE / OWNER TO
That
Perhaps better to put these out towards the end of the dump, not right
after the creation of the object? Or is that what you're doing?
I just inserted the ALTER OWNER statement between the CREATE and the
GRANTs. Why do you want them at the end of the dump?
I would envision the safest procedure
So that the initial owner is still owner when he does COPY, ALTER TABLE
ADD PRIMARY KEY, etc etc. Else you're gonna have problems.
I was thinking of doing all COPY and ALTER as superuser as well...
Or are you trying to make it work when run as non-super? Which is won't
since ALTER OWNER will
The right way to do this at the C level would be to use the same
infrastructure as nextval() does to accept arguments like 'foo' and
'Foo.Bar'. There's no reason to restrict the two-argument form
to the current search_path.
Is it possible to do that in SQL? eg. is there anything you can do
If typTup is of type Form_pg_type, is this use of ObjectIdGetDatum legal?
tuple = SearchSysCache(RELOID, ObjectIdGetDatum(typTup-typrelid),
0, 0, 0);
If not, how do I turn -typrelid into an Oid type for safe passage
through ObjectIdGetDatum?
Is there any reason for this behaviour:
test=# select 1::bit;
bit
-
0
(1 row)
test=# select '1'::bit;
bit
-
1
(1 row)
Chris
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
What about these?
test=# select B'1' 4;
?column?
--
0
(1 row)
test=# select B'1'::bit varying 4;
?column?
--
0
(1 row)
test=# select '1'::bit varying 4;
?column?
--
0
(1 row)
---(end of broadcast)---
TIP 4: Don't
I'd be inclined to make it only take 2 args, table, col where table can be
namespace qualified. This allows people who arn't namespace aware to just do
SELECT pg_get_serial_sequence('mytable','mycol') and have it return the
correct item following searchpath.. I would think this would then
I notice you can use most of the RENAME TO commands in postgres to
rename system objects. Renaming a system table is disallowed:
test=# alter table pg_namespace rename to blah;
ERROR: permission denied: pg_namespace is a system catalog
But mucking with any other system object is not:
test=#
As an example of why superusers should have as few restrictions as
possible, I refer you to the 7.4.2 release notes:
http://www.postgresql.org/docs/7.4/static/release.html#RELEASE-7-4-2
Without the ability for superusers to muck with the system catalogs,
we'd have had no choice but to force initdb
Tablespaces is in the patch queue waiting for Tom's review. Nested
transactions is also in the queue and needs review. Alvaro is working
on implementation of the phantom xid feature, but the patch is probably
ready for application if we think it can all be completed by July 1.
I should have all
d=# update pg_proc set proname = zsubstring(proname,2);
UPDATE 1727
So I say there isn't any reason to prohibit renaming functions just
because they were created at initdb time. The worst-case scenario
is you have to rename 'em back. Likewise for ALTER OWNER.
Again, no reason to stop them doing
3. Or even create a pg_get_sequence() function:
SELECT SETVAL(pg_get_sequence(schema.table, col), 17);
Actually, this is the best solution :)
OK, attached is a pg_get_serial_sequence(schema, table, column) function
. I have tested it with crazy names and it seems to be good. It works
like
(moved to -hackers)
If you use sufficiently long table/field names then different tables
could truncate to the same generated names, and in that case there's
some risk of concurrently choosing the same unique name. But I don't
recall anyone having complained of that since we started using this
Yeah, I know ... we ought to find some way around that, but I dunno
what yet ...
My idea, which I tried hacking, but gave up was to do the following:
1. Extend this command:
ALTER SEQUENCE seqname RESTART WITH 17;
to allow:
ALTER SEQUENCE ON table(col) RESTART WITH 17...
or
ALTER SEQUENCE ON
3. Or even create a pg_get_sequence() function:
SELECT SETVAL(pg_get_sequence(schema.table, col), 17);
Actually, this is the best solution :)
Chris
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
I love PG, I've been using it since version 6x, and it has gotten
fantastic over the years, and in many cases, I would choose it over
Oracle, but for systems that need frequent updates, I have a lot of
concerns.
...that's the price you pay for concurrency man...
Chris
Yep, Tom fixed it good.
Bruce Momjian wrote:
I can confirm that current CVS handles this OK.
---
Christopher Kings-Lynne wrote:
I had a suspicion and it was confirmed:
test=# create table oidtest (a int4, unique(oid));
NOTICE
Was this another of those darn regurgitated-from-February messages?
I'm about ready to go out and acquire missile targeting coordinates
for pcbuddy.com ...
Hmmm, maybe - I don't have the email any more though, as I deleted it :(
I get regurgitated emails all the time - it can be quite confusing...
Seems like a reasonable thing to submit.
Chris
mike g wrote:
Hello,
Going through the regress test sql files I noticed that when testing
string functions there was no upper / lower case tests.
I see upper / lower being used in the select_having and select_implicit
files in the GROUP BY and ORDER
Parsing is a whole nother ball of wax besides lexing. I wasn't planning
to put *that* into psql. Remember the only thing psql really wants from
this is to detect where end-of-statement is ...
OK, I'm not that great at understanding where lexing ands and parsing
starts. These are the things
Doh, sorry. I just realised that the lists just gave me a whole bunch
of mails from back in February, which is when Tom made this post...
Chris
Christopher Kings-Lynne wrote:
Parsing is a whole nother ball of wax besides lexing. I wasn't planning
to put *that* into psql. Remember the only
REVOKE ALL ON TABLE foo FROM GRANTOR [USER] alice;
The super user must really be a *super* user.
I think we need a proper 'effective user' facility.
At the moment, there's breakage if a super user creates a language, then
drops their superuser privs, then the dump cannot be restored.
All other
In SQLite or MySQL there is a statement INSERT OR REPLACE , is something
like this in postgres , or could be ?
No, there isn't and there currently isn't anyone working on adding it.
Chris
---(end of broadcast)---
TIP 9: the planner will ignore your
The problem here is not with pg_dump; the problem is that dropping
privileges doesn't cascade to dropping objects that are dependent on
those privileges. AFAICS the SQL spec requires us to be able to do
the latter.
The spec really requires that?? So basically we have RESTRICT and
CASCADE on
Well, the spec doesn't have create permissions per se, but they do have
a usage right on domains, and they specify that revoking that results
in dropping objects:
7) For every abandoned domain descriptor DO, let S1.DN be the
domain name of DO. The following drop domain
Someone else suggested having pg_dump dump all objects without ownership
(so, on restore, they'd all initially be owned by the user running the
script, hopefully a superuser) and then doing ALTER OWNERs and GRANTs at
the bottom.
Actually, this would probably only be reasonable if you fixed the
How about pg_dumpall dumps all users as superusers, and then changes
them back to what they're supposed to be at the bottom of the script :)
Leaves you in kind of a dangerous state if the script doesn't complete,
doesn't it?
If your script doesn't complete, it can leave you in all sorts of bad
When this new behavior was introduced, and I migrated our databases to
the new PgSQL version (dump/restore), the locale of all my databases
were silently changed from C to US_en. This broke one application in a
very subtle way because of slightly different sort behavior in the
different
This has bitten me a couple times. In what version did it change?
My feeling, and I'd like to see what everyone else thinks, is that if you
do not specify a locale, you get C.
I think that initdb should default to something, and do the following:
* Have an explicit warnign if no locale specified,
I did not modify the format of the zic timezone database files, which
means that for the moment the system will not know about daylight-savings
periods outside the range 1901-2038. Given the way the files are set up,
it's not a simple decision like 'widen to 64 bits'; we have to actually
think
Any reason why postgres can't detect time zone on my machine automatically?
LOG: could not recognize system timezone, defaulting to Etc/GMT-8
HINT: You can specify the correct timezone in postgresql.conf.
postmaster starting
-bash-2.05b$ LOG: database system was shut down at 2004-05-31 15:19:00
Any reason why postgres can't detect time zone on my machine
automatically?
I'm guessing the TZ code isn't identifying Aussie timezones. I'm getting the
same message, albeit Etc/GMT-10. If anyone who hacked on the TZ code
(Magnus, Bruce, ...) wants me to add a few printfs to trace this down,
Hi,
I had this question posed to me on IRC and I didn't know the answer.
If all that is needed to ensure integrity is that the WAL is fsynced,
what is wrong with just going:
wal_sync_method = fsync
fsync = false
??
Chris
---(end of broadcast)---
What I'm thinking about right now is tweaking the index-build code to
write to WAL only if it sees that PITR is actually in use. It would
have to look at the GUC variables to determine whether WAL archiving
is enabled. If archiving isn't turned on, then we could assume that
rollforward from a
Is there any possibility for changing view column types now in 7.5?
Chris
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Nearing our June 1 feature freeze, we now have patches for all our major
outstanding features. Their status is:
Win32 - almost done, fsync needed, bug fixes, etc
PITR - Simon is away until Sunday and we are working on it
Nested transactions - patch needs review, trigger
I've encountered a situation where I'd like to store some information
about the database when I do a pg_dump. For instance, the timestamp of
the dump. And some other information that I pull from the database.
I think every dump should dump the timestamp regardless...
Chris
I am updating the ScanLists so they can be dropped at subtransaction
abort. Currently they are stored with custom next pointers; however
it would be much cleaner to deal with deleting single items if they were
Lists. Is it OK if I change them to be so?
Alvaro,
Have I mentioned that you're a
It wouldn't; you'd need vacuum full to collapse out the dead space.
You could also try CLUSTER which is faster than VACUUM FULL when most
of the data has to be moved anyway. (Some days I think we should dump
VACUUM FULL, because it's optimized for a case that's no longer very
interesting...)
Out
BTW: Is there a public spec on what will be tablespace compatible and how?
For instance: will is be possible to create a table on a separate
tablespace than the DB? Will it be possible to create an index on a
separate tablespace than the table?
(Since Gavin hasn't replied yet)
1. There are two
I am actually
working on fulltext search program of my own.
No need, use contrib/tsearch2
Chris
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
- anything else?
All compiles and passes regression tests on FreeBSD...
Chris
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message
Hi guys,
A guy on the IRC channel managed to accidentally click the wrong thing
in phpPgAdmin and managed to remove superuser privileges from his only
superuser.
We thought and though but it seems that there is no way to recover from
this situation except a re-init and reload. And what user
No sweat; we've seen this one before.
Should this situation be prevented though?
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
The mistake has only come up two or three times that I can remember,
which doesn't elevate it to the category of stuff that I want to install
a lot of mechanism to prevent. Especially not mechanism that would get
in the way of reasonable uses. I think it's sufficient to have a
recovery
Isn't it just enough to prevent the user with userid 1 from losing the
superuser status. If one want to allow it one could prevent it just when
doing the ALTER USER stuff and allow it when editing pg_shadow directly.
Or maybe have some guc variable that write locks the user with id 1.
That gets
IMHO we (that is Christopher, me and others maintaining easy to (mis)use
tools) should warn the users about what they're going to do.
Yes, I'm going to have to modify phpPgAdmin methinks.
Chris
---(end of broadcast)---
TIP 9: the planner will ignore
Ingres is to be released as open source:
http://developers.slashdot.org/article.pl?sid=04/05/25/0043219mode=nestedtid=126tid=137tid=163tid=185tid=198
Like the article says, I wonder if these is any synergy between the
products. ie. Can we grab features from their codebase?
Chris
Hi,
There are several objects in PostgreSQL that you can only create if you
are a superuser, eg. procedural languages.
If you do this, you break the dump:
1. create a superuser
2. install a language as that superuser
3. drop the superuser privs from that superuser
4. dump the database
5. attempt
I get this since Tom's commit.
Chris
--- ./results/horology.out Sun May 23 11:39:49 2004
***
*** 1787,1796
| Wed Mar 15 13:14:02 2000 PST | @ 34 years| Tue Mar 15
13:14:02 1966 PST
| Sun Dec 31 17:32:01 2000 PST | @ 34 years
It seems to me that we shouldn't copy them, but I'm having a hard time
putting a finger on why exactly. I guess it goes along with the fact
that we don't copy the database's owner, and any per-database variable
settings seem to me to be the database owner's decision to make.
Good points. The
Hi guys,
The latest thing we've noticed in the IRC channel and the phpPgAdmin
lists is that people want to be able to grant on all objects in a
database, etc:
grant select on all tables to blah;
or even:
grant rule on all views in schema myschema to blah;
This seriously is asked every other day
Here are the two syntaxes we can use for turning off clustering:
1) ALTER TABLE / SET WITHOUT CLUSTER
This will turn off clusting on any index on the table that has it
enabled. It won't recurse so as to match the CLUSTER ON syntax.
However, this form makes the non-standardy SET WITHOUT form
I get this since Tom's commit.
On what platform? How is type time_t defined on your platform?
Hmmm, I just CVS up'd and all regression tests now pass...
Chris
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Anyone thought about this at all yet? Is it possible to have
per-database variables that refer to the current database in someway
that would need to be altered to refer to the new db?
Chris
Christopher Kings-Lynne wrote:
Hi,
When you do this:
CREATE DATABASE test TEMPLATE master;
It doesn't
Hi,
When you do this:
CREATE DATABASE test TEMPLATE master;
It doesn't copy any per-database GUC variables, created by the ALTER
DATABASE master SET var TO val; command.
Should it?
Chris
---(end of broadcast)---
TIP 7: don't forget to increase
I get this:
gmake[4]: Entering directory
`/space/1/home/chriskl/pgsql/src/backend/access/gist'
gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes
-Wmissing-declarations -I../../../../src/include -c -o gist.o gist.c -MMD
In file included from
That would be great if a C function could find out what schema it had
been declared in, but I don't think it can readily do so.
TODO candidate ?
Seems like it would be a good thing.
Chris
---(end of broadcast)---
TIP 7: don't forget to increase your
'k, let me look into it when I get back ... but some of those diffs would
be humongous, no? Ah well, let me look, I can try it out and if nobody
likes it, can always disable the diffs again afterwards ...
Showing diffs will also allow more eyes to find little bugs in the patches.
Chris
Not sure if I like the URLs, myself ... opinions?
Can the urls point to the exact diff?
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Not sure if I like the URLs, myself ... opinions?
Can the urls point to the exact diff?
Already fixed for that ...
Yeah sorry - i hate reading this conversations in a later timezone :( I
really should have learned by now :/
Chris
---(end of
You may not distribute this tool without the express written
permission of Mark Russinovich.
Then by no means should *any* of that code be included into PostgreSQL. In
fact, comments should not even make reference to it.
May I point out that there is a heap of debate about whether or not we
can
I don't think we have ever changed oids for existing data types, so you
should be OK.
Are you sure? If we remove a type, then its oid becomes up for grabs by
the unused_oids script.
We have removed a few functions in 7.4 (oidsrand, etc.) and I wouldn't
be surprised if we haven't _already_
True, but have we ever removed types? I can't think of one.
Hmmm...perhaps.
We have removed a few functions in 7.4 (oidsrand, etc.) and I wouldn't
be surprised if we haven't _already_ reused those oids...
Yes, for functions that is very true.
I wonder if that has any implications for future
Not sure. Most of the system stuff is loaded in a pretty good order, and
cluster is only good if you are going after seveal rows of identical
value or similar value in the same table, and I can't think of a case
where this would help. Can others? It is a good question.
pg_attribute would
Seriously though, we all have the roles that we play. I don't think
redirecting specific resources to other
resources will help beyond slowing up the original resources.
And now Neil's on holidays :)
Perhaps we need more committers. The deluge of patches is starting to
strain the major
I run psql and I get this:
-bash-2.05b$ psql template1
Welcome to psql 7.5devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q
I get a similar failure running pg_dumpall and initdb as well.
Chris
Christopher Kings-Lynne wrote:
I run psql and I get this:
-bash-2.05b$ psql template1
Welcome to psql 7.5devel, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
He is waiting for nested transactions to be committed so he can merge
his work in.
Somebody has posted sync multimaster replication (PgCluster) - nobody
has commented on that. Maybe I am the only one who has ever tried it ...
I think it should be on gborg.
You mean pgFoundry :)
Chris
Alternative database location:
Should this code be removed now?
I think that this:
CREATE DATABASE blah LOCATION 'xyz';
Should now be interpreted to mean:
CREATE TABLESPACE blah_tbsp LOCATION 'xyz';
CREATE DATABSE blah TABLESPACE blah_tbsp;
Or something like that...
Chris
Jan, correct me if I'm wrong ... Jan's point is that we have enough
already to warrant a beta on June 1st, even without Win32 ... Win32 (or
any of the other stuff, like PITR/tablespaces) would be icing on the cake
...
I think we're close enough on win32 to wait for it. It would look bad
for us
This is 7.4.1, server and client.
Then see sslmode in the libpq docs.
Hmmm. I wonder how that's exposed via PHP's connect function...
Chris
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
Schema | Name | Type |Access privileges
+-+---+--
public | mytable | table | {miriam=a*r*w*d*R*x*t*/miriam,=r/miriam}
Changing the default ACL would take this down to
public | mytable | table |
Did you guys find any solution to this in the end?
Chris
Oleg Bartunov wrote:
Thanks Christopher,
we'll look into the issue.
Oleg
On Fri, 7 May 2004, Christopher Kings-Lynne wrote:
I have a table with a tsearch2 index on it. Now, I have all the
tsearch2 stuff installed into a 'contrib
OK, I'll try to explain it better.
1. Tsearch2 requires access to several tables.
2. You can edit the tsearch2.sql script and change the set schema =
... to contrib.
3. You load all the tsearch2 objects into contrib.
4. You create a table in the public schema with a column of type
6. However, it is now not possible to restore the sql script as it was
dumped, as you get this error:
ERROR: relation pg_ts_cfg does not exist
No problem,
[EMAIL PROTECTED]:~/app/pgsql/tsearch2/test_scheme$ createdb qq
CREATE DATABASE
[EMAIL PROTECTED]:~/app/pgsql/tsearch2/test_scheme$ psql
No problem,
Actually, I did some more testing and I properly understand the problem
now - and it won't happen in the general restoring case.
What fails is if you pg_dump -a to just dump the DATA from a table
containing a tsearch2 trigger that is in a different schema.
Then you delete all the
Hi Thomas,
Please don't get too disheartened that a developer hasn't commented on
your stuff yet. Everyone's very busy at the moment. Just hang in there!
Chris
Thomas Hallgren wrote:
Yes, this is another vain attempt to get some attention to the custom
config variables patch that I've
It could not. I think the fundamental point here is that it is a real
bad idea for the tsearch routines to make any assumptions about the
current search path. What I would suggest is that the internal objects
used by the tsearch routines (such as pg_ts_cfg) should be required to
live in a
That would be great if a C function could find out what schema it had
been declared in, but I don't think it can readily do so.
There's no context information available to it at all? Even if you go
contrib.tsearch2 qualfication?
How about making it so that the default context for functions is
701 - 800 of 2006 matches
Mail list logo
