Re: [SQL] Pgaccess problem on RedhatLinux9
On Tuesday 23 December 2003 05:49, vijaykumar M wrote: > Hi All, > Previously i was used RedhatLinux7.2 & Postgres7.4, that time i'm able > to use the pgaccess command to view the tables. >Presently, i'm using RedhatLinux9 & Postgres7.4, here i'm not able to > use the pgaccess command. It is saying "command not found." > > One thing, i observed was on RedhatLinux7.2,this pgaccess is available > at \usr\share\pgsql\pgaccess.this is missing at redhatlinux9. > > Is there any way to use pgaccess on Redhatlinux9. I don't think pgaccess is installed as standard on any version of RedHat. Downloads are from: http://www.pgaccess.org/ You might also want to look at pgadmin (v3) - http://pgadmin.postgresql.org/pgadmin3/index.php There are also other options: phpPgAdmin (web-based), pg-manager? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] restoring database
Ð ÐÐÐ, 22.12.2003, Ð 18:53, scott.marlowe ÐÐÑÐÑ: > On Mon, 22 Dec 2003, Theodore Petrosky wrote: > > > As I am not someone with a lot of experience > > (depending on the size of the 'dumpall') I would > > create a new database to suck up the 'all' then > > pg_dump the table I really cared about. > > > > Obviously, if the dump_all is a terabyte database this > > method is inconvenient (to say the least). > > > > However it will work. > > In the past I've used > > 'cat -n filename'|grep -4 database > > to find the starting points of individual databases etc... and then used > split to break it into the exact right size pieces to do this. That sounds like something pg_dump / pg_dumpall should be able to do itself, don't you think? -- Markus Bertheau <[EMAIL PROTECTED]> ---(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: [SQL] INHERITS and Foreign keys
Hi Pedro; I understand that at the moment it is more of a design limitation than a bug. I think we should vote the desired behavior as a feature request, however. Best Wishes, Chris Travers - Original Message - From: "Pedro" <[EMAIL PROTECTED]> > >> Foreign keys, unique and primary key constraints do not meaningfully > >> inherit currently. At some point in the future, that's likely to change, > >> but for now you're pretty much stuck with workarounds (for example, using > >> a separate table to store the ids and triggers/rules on each of the > >> tables > >> in the hierarchy in order to keep the id table in date.) > > hi > > same problem here on 7.4 > can we vote for this bug somewhere ?! > > thanks for your time > Pedro > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Historic Query using a view/function ?
...Postgresql 7.2...
I'm building the history of a table using rules. I've been trying to figure out
a way to select on a table as it would have appeared in a point in time. I
can't seem to wrap my brain around the problem, tho.
Given some tables
CREATE TABLE list (
num int4 NOT NULL,
name varchar(50),
type varchar(50),
modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone,
CONSTRAINT list_pkey PRIMARY KEY (num)
) WITH OIDS;
CREATE TABLE list_log (
num int4 NOT NULL,
name varchar(50),
type varchar(50),
modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone,
mod_type varchar(3),
log_date timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone
) WITH OIDS;
And some rules...
CREATE RULE list_del AS ON DELETE TO list DO INSERT INTO list_log (num, name,
type, modified, mod_type) VALUES (old.num, old.name, old.type, old.modified,
'D'::"varchar");
CREATE RULE list_upd AS ON UPDATE TO list WHERE ((old.name <> new.name) OR
(old.type <> new.type)) DO INSERT INTO list_log (num, name, type, modified,
mod_type) VALUES (old.num, old.name, old.type, old.modified, 'U'::"varchar");
It'd be great to be able to do something like...
SELECT * FROM hist_list('10/10/2003'::date) WHERE name like '%Jones';
... I don't think Functions can return tables in 7.2 ... Can anyone think of a
way around this?
CG
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Distributed keys
Hello all! An area of the relational database model that is not included in the SQL standard are distributed keys and distributed foreign keys. A quick example (Case 1): employees (id, name); schools (name, location); teachers (employee_id, school_name); subs (employee_id); managers (employee_id, school_name); with constraints unique employees(id) teachers(employee_id) references employees(id) teachers(school_name) references schools(name) subs(employee_id) references employees(id) managers(employee_id) references employees(id) The idea is that employees must be in one (and only one) of either teachers, subs, or managers. Currently, this might be represented in something like (Case 2) employees (id, name, employee_type, school_name); schools (name, location); employee_types (type); with constraints employees(employee_type) references employee_types(type) employees(school_name) references schools(name) where employee_types includes "teacher", "sub", and "manager" Or it might be represented with a number of rules or triggers that perform all of the necessary checking. employees(school_name) can't have a not null constraint because if the employee is a sub, they aren't associated with a school. Using the terms "distributed key" and "foreign distributed key", in the first case employee_id is a "distributed key" in that it must occur in only one of the tables teachers, subs, or managers. Distributed keys are similar in concept to primary keys—they must be unique. This guarantees an employee_id in teachers is not found in subs or managers, an employee_id in subs is not found in managers or teachers, and an employee_id in managers is not found in subs or teachers. employees(id) is a foreign distributed key in teachers, subs, and managers (as employee_id). Foreign distributed keys are similar in concept to foreign keys in that employees(id) must be referenced by a single tuple in one of teachers, subs, or managers. Another use would be in this situation (something I'm working on right now): I want to link comments by employees by employee_id, but comments from non-employees by name (as they don't have an id). comments(id, comment); comments_nonemployees(comment_id, name); comments_employees(comment_id, employee_id); with constraints comments_nonemployees(comment_id) references comments(id) comments_employees(comment_id) references comments(id) and comments(id) must be listed in either comments_nonemployees(comment_id) or comments_employees(comment_id) I haven't looked very far into how to implement distributed keys and foreign distributed keys in PostgreSQL beyond briefly looking at the pg_constraint system table, thinking a distributed key would be something making employee_id unique in teachers(employee_id) UNION subs(employee_id) UNION managers(employee_id). A distributed key is distributed over a number of tables, rather than a single one, so there'd have to be a list of relid-attnum pairs, rather than a single relid-attnum pair, such as conrelid and conkey in pg_constraint. Here's a brief sketch of the idea: pg_distributed distname name the name of the distributed key constraint distrelid oid the relid of one of the tables involved in the distributed keys distkey int2[] a list of the attnum of the columns of the table with oid distrelid involved in the distributed key distforkey bool true if foreign distributed key distfrelid oid if a foreign distributed key, the relid of the the referenced table, else 0 distfkey int2[] if a foreign distributed key, a list of the attnum of the columns of the table with oid distfrelid referenced by the foreign distributed key, else 0 In pg_distributed, distname, distfrelid, distfkey would be the same in every tuple involved in the distributed key, while distrelid and distkey would vary. Basically a one-to-many relation. These are just some thoughts, and the first time I've looked at the PostgreSQL internals, so I wouldn't be suprised if this approach is wrongheaded. I know PostgreSQL endeavors to adhere closely to the SQL standard, and I think this is important as SQL *is* a standard. One of the things that makes PostgreSQL great in my opinion is that it goes beyond the standard in some areas where users and developers have found useful, such as the PostgreSQL rule system. I think distributed key support would extend the usefulness of PostgreSQL without hindering SQL conformance. I'm interested in hearing what others have to say, especially along the lines of implementation. I haven't seen much discussion of distributed keys on the lists (other than Josh Berkus), so perhaps there isn't much interest. Would there be any foreseeable opposition if I (and/or others) worked on this? Regards, Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [E
Re: [SQL] Distributed keys
Michael Glaesemann <[EMAIL PROTECTED]> writes: > I'm interested in hearing what others have to say, especially along the > lines of implementation. I haven't seen much discussion of distributed > keys on the lists (other than Josh Berkus), so perhaps there isn't much > interest. Would there be any foreseeable opposition if I (and/or > others) worked on this? This seems to have considerable overlap with the problem of indexing inheritance hierarchies (so that constraints on tables with children would work as people expect). It may be that it's sufficient to solve it for inheritance cases, and not try to support the generic case of constraints applied across arbitrary sets of tables. ISTM that the latter could introduce a bunch of extra definitional and practical issues beyond what you'd have to solve to do the former. You're discussing it on the wrong list though --- try -hackers. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how do i get differences between rows
On Thu, Dec 18, 2003 at 12:21:35 +, teknokrat <[EMAIL PROTECTED]> wrote: > I am after an sql query that can give the differnces between consecutive > timestamp values in the resultset. > anyone know how to do this? I think you can do something like the following (untested) on most systems. select a.stamp - (select max(stamp) from tablename where tablename.stamp < a.stamp) from tablename; For postgres this (also untested) might be faster if there is an index on stamp. select a.stamp - (select stamp from tablename where tablename.stamp < a.stamp order by stamp desc limit 1) from tablename; The above relies on timestamps being unique. The difference for the smallest timestamp will be null. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] how to preserve \n in select statement
Isn't the simple answer to use bind variables? SQL using bind variables instead of making a new SQL string each time will prevent malicious users from invoking functions and inserting other sql, as well as handle the original problem regarding storage of newlines vs \n. I don't know much about Postgres' SQL cache, but it is well known in Oracle circles that using bind variables is is a critical part of system design, not just for security, but for performance and scalability. I suspect that the same issues apply more or less to postgres. Correct me if I'm wrong, please... regards Iain - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Denis" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, December 22, 2003 7:48 PM Subject: Re: [SQL] how to preserve \n in select statement > On Monday 22 December 2003 09:37, Denis wrote: > > Hi Richard.. > > > > If your users are required to fire only SELECT and no DML, you can do the > > following: > > > > BEGIN; > > execute the statements given by user > > ROLLBACK; > > > > This will not affect your SELECT and also if any malicious user gives > > DELETE statement, that will not have any impact too.. > > An interesting idea, though you'd need to be careful with side-effects > (triggers/functions etc). I seem to recall a "read-only" setting being > discussed for transactions too (though not as a security measure, I should > emphasise). > > The other thing is to use the database user/group mechanism - something which > tends to be neglected with web-based apps (partly because different DBs have > different setups here). > If only an application super-user can add/delete users make sure the > permissions reflect this and connect as a more restricted user for other > logins. > > -- > Richard Huxton > Archonet Ltd > > ---(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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
