Re: [SQL] psql: no schema info
chester c young wrote: using 8.2 and 8.3 here's (psychological) problem as I see it: # set search_path=old_schema; # # create sequence new_schema.seq1; # # create table new_schema.table1( #col1 integer default nextval( 'seq1' ) # ); using old_schema.seq1, not new_schema.seq1 and imho to make matters more difficult to troubleshoot: # \dt table1 -> does not show which schema for seq1 Must admit I thought you were wrong, but having tested it, I think I see what you mean. If the schema in question is in the search_path then it doesn't get displayed. If you've forgotten what your search_path is set to then that can cause confusion. Not a problem I see much of since I very rarely change my search_path. I refer to schema.table by preference. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] psql: no schema info
chester c young wrote:
> # create table new_schema.table1(
> #col1 integer default nextval( 'seq1' )
> # );
>
> using old_schema.seq1, not new_schema.seq1
Yes, that's correct -- assuming you had an old_schema.seq1 sequence too.
> and imho to make matters more difficult to troubleshoot:
>
> # \dt table1 -> does not show which schema for seq1
I agree it can be confusing if you're not looking for it.
alvherre=# set search_path to old_s;
SET
alvherre=# \d new_s.table1
Tabla «new_s.table1»
Columna | Tipo | Modificadores
-+-+---
col1| integer | default nextval('seq1'::regclass)
Here, the nextval() is correctly _not_ qualified, because the current
search path is the sequence's schema. But it is certainly confusing.
You have to set the search_path to the table's search path for the
problem to be obvious:
alvherre=# set search_path to new_s;
SET
alvherre=# \d new_s.table1
Tabla «new_s.table1»
Columna | Tipo | Modificadores
-+-+-
col1| integer | default nextval('old_s.seq1'::regclass)
alvherre=# \d table1
Tabla «new_s.table1»
Columna | Tipo | Modificadores
-+-+-
col1| integer | default nextval('old_s.seq1'::regclass)
I'm not sure what's a good solution here. Perhaps the \d command should
temporarily set the schema to something that would cause regclass to
display qualified names all the time, when you passed it a qualified
name (using SET LOCAL perhaps, but reverting to the original value after
then \d is done). You can't just use a nonexistant schema or some kind
of NULL or empty value, because SET rejects it. I can set it to $user,
which is accepted but doesn't exist on my scratch database:
alvherre=# set search_path to '$user';
SET
alvherre=# \d new_s.table1
Tabla «new_s.table1»
Columna | Tipo | Modificadores
-+-+-
col1| integer | default nextval('old_s.seq1'::regclass)
Another option would be to set it to the given schema, so that any name
not on that schema is qualified.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trim(both) problem?
Thanks a lot for all help! I understand how trim work now.
You could probably use instead:
select replace('ROI Engineering Inc.', '', '')
That would zap occurrences in the middle of the string, though.
regexp_replace would be better since it'd allow anchoring the
pattern, eg
select regexp_replace('ROI Engineering Inc.', '^', '');
select regexp_replace('ROI Engineering Inc.', '$', '');
This is exactly I am looking for, but my version
PostgreSQL 8.0.15 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
does not support this func, and have to think about the other way to
'trim' the ^ & $
Thank you again!
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] psql: no schema info
> > # \dt table1 -> does not show which schema info. was wrong on this - \dt shows schema for relations _not_ in the search path. my new good practice is to keep search_path = PUBLIC so all schema info is displayed always all the time invariably. Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] information_schema.referential_constraints permissions
hello, I have some code that gets foreign key information from information_schema.referential_constraints. I was puzzled about why it wasn't returning anything for a while, until I read the information_schema.sql file and the documentation carefully: it has a pg_has_role(c.relowner, 'USAGE') in it and the documentation states "The view referential_constraints contains all referential integrity (foreign key) constraints in the current database that belong to a table owned by a currently enabled role". Fair enough. But then I read the public draft of SQL 2003 and 200n on this view, and it speaks only of "tables in this catalog that are accessible to a given user or role", rather than ownership. the user I was working with most definately had access. it could also read the pg_* system catalog, such that if I copy/paste the view definition without the pg_has_role() line in it (and massage it a bit to remove the CASTs to sql_identifier and character_data) that user got just what I was expecting. so, is that restriction correct? Lodewijk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] currval of sequence "my_seq" is not yet defined in this session
Hi all, I am writing some code to get the current value of a sequence using the currval function to use this value in a following insert on another table. But I am having this message "currval of sequence "my_seq" is not yet defined in this session". Can someone explain me what is the problem with that and by the way explain me the definition of a session in postgres. I have tried to avoid he problem using a stocked function and a trigger but I really want to avoid using triggers for debugging issues. Thanks to all Nacef
[SQL] Truncate table at a certain size.
Hello,
What would be the best method to truncate a table once it reaches a
certain size.
For instance, a table named log. I can check the size of the log;
db=# select pg_relation_size('log');
pg_relation_size
--
8192
(1 row)
What I would like to do is
If table log, is greater than bytes
then truncate table log
Maybe I'll dump it to a flat file first, (yes, it should really be a
flat file to start with, and not in a db.)
Thank you.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] information_schema.referential_constraints permissions
"Lodewijk Voege" <[EMAIL PROTECTED]> writes: > I have some code that gets foreign key information from > information_schema.referential_constraints. I was puzzled about why it wasn't > returning anything for a while, until I read the information_schema.sql file > and the documentation carefully: it has a pg_has_role(c.relowner, 'USAGE') in > it and the documentation states "The view referential_constraints contains all > referential integrity (foreign key) constraints in the current database that > belong to a table owned by a currently enabled role". > Fair enough. But then I read the public draft of SQL 2003 and 200n on this > view, and it speaks only of "tables in this catalog that are accessible to a > given user or role", rather than ownership. the user I was working with most > definately had access. The SQL committee changed that recently --- SQL92 and SQL99 define the view as Identify the referential constraints defined in this catalog that are owned by a given user. I don't think we've gotten around to trying to sync information_schema with SQL2003. (The whole concept that information_schema might be a moving target is pretty disturbing :-() regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Truncate table at a certain size.
On Tue, Apr 29, 2008 at 4:05 AM, Dana Huggard - Navarik <
[EMAIL PROTECTED]> wrote:
> Hello,
>
> What would be the best method to truncate a table once it reaches a
> certain size.
>
> For instance, a table named log. I can check the size of the log;
>
> db=# select pg_relation_size('log');
> pg_relation_size
> --
> 8192
> (1 row)
>
>
> What I would like to do is
>
> If table log, is greater than bytes
> then truncate table log
>
No, you wouldn't want to do this First and foremost, SQL (and Postgres)
does not guarantee that the new rows you are inserting land at some specific
point in storage; so you can't really be sure which part you want to
truncate.
But by design, in an ever growing table, Postgres puts new rows at the tail
end; so even with this knowledge you don't want to truncate the table,
because your older rows are towards the head/beginning of th table and not
at the tail.
Maybe I'll dump it to a flat file first, (yes, it should really be a
> flat file to start with, and not in a db.)
>
>
Yes, that'd be a better approach; use two files of fixed size, and cycle
between them (much like how Postgres deals with it's transaction logs (in
pg_xlog/)).
Best regards,
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [SQL] currval of sequence "my_seq" is not yet defined in this session
On Mon, Apr 28, 2008 at 4:33 PM, Nacef LABIDI <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I am writing some code to get the current value of a sequence using the
> currval function to use this value in a following insert on another table.
> But I am having this message "currval of sequence "my_seq" is not yet
> defined in this session".
>
> Can someone explain me what is the problem with that and by the way explain
> me the definition of a session in postgres.
>
> I have tried to avoid he problem using a stocked function and a trigger but
> I really want to avoid using triggers for debugging issues.
By your mentioning sessions I assume you've read the bit of the docs
on sequence functions, and are somewhat familiar with the three
functions, nextval, currval, and setval.
Now, mental exercise time. You connect to the database. This is YOUR
session. I connect to the same database. This is my session. There
is a sequence that will give out 6 as its next value. The last value
it gave out (to someone other than either of us) was 5. We both
execute " select currval('someseq'); "
What should we get back? How would we use it in a safe way if we got
it? Can you see the problem here? The current value, if we could get
it, would be the same, and neither of us could safely do anything with
it. What we need is the NEXT value. you select nextval('someseq')
and so do I. Let's suppose you get 6, and I get 7. It could be the
other way around, but we don't care. We can both go off and do what we
want with our own values. After the nextval, currval for you will
return 6, and for me it will return 7.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Truncate table at a certain size.
On Mon, Apr 28, 2008 at 7:24 PM, Gurjeet Singh <[EMAIL PROTECTED]> wrote:
> On Tue, Apr 29, 2008 at 4:05 AM, Dana Huggard - Navarik
> <[EMAIL PROTECTED]> wrote:
>
> > Hello,
> >
> > What would be the best method to truncate a table once it reaches a
> > certain size.
> >
> > For instance, a table named log. I can check the size of the log;
> >
> > db=# select pg_relation_size('log');
> > pg_relation_size
> > --
> > 8192
> > (1 row)
> >
> >
> > What I would like to do is
> >
> > If table log, is greater than bytes
> > then truncate table log
> >
>
> No, you wouldn't want to do this First and foremost, SQL (and Postgres)
> does not guarantee that the new rows you are inserting land at some specific
> point in storage; so you can't really be sure which part you want to
> truncate.
I think the OP was talking about running the truncate command on them...
if select pg_relation_size('log') > somesize then truncate log;
If that's the case he can either iterate a list of tables in plpgsql,
an external scripting language, or write some select statement that
creates truncates for all the tables over x size.
something like:
select 'tuncate '||relname||';' from (rest of query from psql -E and
\d here) where pg_relation(relname) > somesize;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
