Re: [GENERAL] Column limitation?
Kandy Wong wrote: Hi, I'd like to know is there a column limitation for PostgreSQL? like column size and maximum number of columns per table? Thank you. Kandy ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster http://www.postgresql.org/about/ ---(end of broadcast)--- TIP 1: 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: [GENERAL] thank you
Gregory Williamson wrote: Kevin H. wrote on Tue 12/18/2007 7:26 PM Hullo List, This is aimed at everyone in this community who contributes to the Postgres project, but especially at the core folks who continually make this community great through energy, time, money, responses, and what-have-you. ...snipped... The point is that I hope you realize just how much you all mean to the community. +1 Greg Williamson +2 I'm just disappointed that I finish up work with my current employer on Friday and where I am going I won't get to work with PG anymore and thus won't have as much opportunity to interact with the PG community. That is until I can convince my new employer to realise the dark side of Microsoft SQL Server. :) -- Paul Lambert Database Administrator AutoLedgers - A Reynolds Reynolds Company ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Error accessing db with psql
I wrote some records to a database to do some testing, which worked:
AutoDRS=# insert into job_classification
(dealer_id,date_changed,time_changed,jo
b_id) VALUES ('F65','1-Jul-2007','00:00',generate_series(1,100));
INSERT 0 100
AutoDRS=# insert into job_classification
(dealer_id,date_changed,time_changed,jo
b_id) VALUES ('F65','1-Jul-2007','00:00',generate_series(200,900));
INSERT 0 701
But then I immediately went to select from the table:
AutoDRS=# select count(*) from job_classification;
WARNING: could not write block 51773 of 16441/16443/16907
DETAIL: Multiple failures --- write error might be permanent.
ERROR: could not open relation 16441/16443/16907: No such file or directory
CONTEXT: writing block 51773 of relation 16441/16443/16907
AutoDRS=# select * from job_classification;
ERROR: could not open relation 16441/16443/2662: No such file or directory
AutoDRS=# vacuum full analyze;
ERROR: could not open relation 16441/16443/16907: No such file or directory
CONTEXT: writing block 51776 of relation 16441/16443/16907
Now I can't even change to the main postgres database...
AutoDRS=# \c postgres
FATAL: could not open relation 16441/16443/16700: Permission denied
CONTEXT: writing block 56798 of relation 16441/16443/16700
Previous connection kept
AutoDRS=# \q
And now I can't even reconnect at all...
C:\Program Files\PostgreSQL\8.3\binpsql -U postgres
Password for user postgres:
psql: FATAL: could not open relation 16441/16443/16907: No such file or
directo
ry
CONTEXT: writing block 51779 of relation 16441/16443/16907
My pg_log is full of similar messages:
T:2007-12-07 15:45:52.781 WST D: U: ERROR: could not open relation
16441/16443/16700: No such file or directory
T:2007-12-07 15:45:52.781 WST D: U: CONTEXT: writing block 56799 of
relation 16441/16443/16700
T:2007-12-07 15:45:52.781 WST D: U: WARNING: could not write block
56799 of 16441/16443/16700
T:2007-12-07 15:45:52.781 WST D: U: DETAIL: Multiple failures ---
write error might be permanent.
T:2007-12-07 15:45:53.781 WST D: U: ERROR: could not open relation
16441/16443/16700: No such file or directory
T:2007-12-07 15:45:53.781 WST D: U: CONTEXT: writing block 56799 of
relation 16441/16443/16700
T:2007-12-07 15:45:53.781 WST D: U: WARNING: could not write block
56799 of 16441/16443/16700
T:2007-12-07 15:45:53.781 WST D: U: DETAIL: Multiple failures ---
write error might be permanent.
T:2007-12-07 15:45:54.781 WST D: U: ERROR: could not open relation
16441/16443/16700: No such file or directory
T:2007-12-07 15:45:54.781 WST D: U: CONTEXT: writing block 56799 of
relation 16441/16443/16700
T:2007-12-07 15:45:54.781 WST D: U: WARNING: could not write block
56799 of 16441/16443/16700
T:2007-12-07 15:45:54.781 WST D: U: DETAIL: Multiple failures ---
write error might be permanent.
I assume I somehow fubar'd something.
I've checked permissions on all the files and the postgres user that the
service runs under has full control of all directories.
One thing I did do was create a directory on a different disk, called
pg_xlog, shutdown pg, copied all files from the existing pg_xlog under
the data directory and deleted that and recreated it as a junction
point. At that stage I had neglected to create the archive_status
directory which pg did start complaining about:
T:2007-12-07 15:00:43.718 WST D: U: FATAL: could not open archive
status directory pg_xlog/archive_status: No such file or directory
T:2007-12-07 15:00:43.718 WST D: U: LOG: archiver process (PID 7432)
exited with exit code 1
T:2007-12-07 15:01:23.484 WST D: U: FATAL: could not open archive
status directory pg_xlog/archive_status: No such file or directory
T:2007-12-07 15:01:23.484 WST D: U: LOG: archiver process (PID 5444)
exited with exit code 1
It was creating files in the pg_xlog directory, and after creating the
archive_status directory it is now archiving them off to my WAL_Archive
directory.
So.. what have I done? Why did my insert statement claim it inserted all
the records when the logfile tells me that it couldn't write the blocks.
This is a fresh install of pg 8.3-beta4 on weendoze server 2003 (new
server too, no pre-existing database system) so I can just blast it and
restart, but I'm curious to know what I accidentally deleted since I'm
99.41421356237309504% sure I didn't delete anything other than some
files out of my WAL_Archive directory.
--
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds Reynolds Company
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Archiving problem on Windows
Christian Rengstl wrote: show config_file points to the right one. I restarted the server after every change. After including the quotation marks as in the docs, the quotation marks also appeared in the command, so at least it shows that the config file was read. How about show archive_command;? Just to verify that it's reading in the right thing. e.g. My postgresql.conf: archive_command = 'copy %p E:\\PostgreSQL\\WAL_Archive\\%f' From pgsql: postgres=# show archive_command; archive_command copy %p E:\PostgreSQL\WAL_Archive\%f Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds Reynolds Company ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Archiving problem on Windows
Christian Rengstl wrote: The archive_command is 'copy %p C:\\Archive\\DBArchive\\%f', nevertheless I changed it to your version, but without success. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings And what does pgsql show when you do a show archive_command; -- Paul Lambert Database Administrator AutoLedgers - A Reynolds Reynolds Company ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] log_line_prefix='%t %u %d %h %p %i %l %x ' causes error
Andrus wrote: log_line_prefix='%t %u %d %h %p %i %l %x ' I checked and all options are legal. That works fine for me... are you sure log_line_prefix is line 482 in your config file? You might have inadvertently put a superfluous % somewhere else. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds Reynolds Company ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Query re disk usage
Paul Lambert wrote: I've just noticed in the tablespace documentation (Ch 19.6) that PG makes use of symbolic links that point to any user-defined tablespaces but AFAIK W2K3 doesn't support symlinks. OK, W2K3 supports a thing it calls Junctions, which are similar to symlinks - and PG appears to be using that in this case. Crisis averted. Cheers, P. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds Reynolds Company ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Query re disk usage
Magnus Hagander wrote: On Thu, 2007-11-22 at 17:04 +0900, Paul Lambert wrote: OK, W2K3 supports a thing it calls Junctions, which are similar to symlinks - and PG appears to be using that in this case. Crisis averted. I was just going to suggest that. It's a pretty neat feature, but the support in the GUI for knowing when you're entering one is nonexistant. If you do dir on the commandline it'll tell you the truth. Yep, I noticed that... took me a while to find it though. Cheers for the assistance Magnus. P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] POLL: Women-sized t-shirts for PostgreSQL
Joshua D. Drake wrote: We do not yet have a store although it has been something of interest in the past. We usually order them in bulk and then request donations for them at the conferences and shows. Note :) If you register for East you get a shirt. What if we register and don't show up... do we still get the shirt? :) I'd definitely like some PG shirts, polo and tee. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds Reynolds Company ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Query re disk usage
I'm running PG 8.3beta3 on a W2K3 server. I've set up a tablespace on D drive, with PG itself on C drive and loaded a bunch of data into a database to test. The directory I've created the tablespace in on D drive grows to 116Mb - which would be about right for the amount of data I've plugged in. (pg_size_pretty(pg_database(size()) certainly corroborates that value anyway) I note however, that the pg_database directory on C drive also grows at the same time to 116MB. If I truncate the table I've added the data to, both directories shrink down to around 7 or so Kb. Why is PG apparently storing my data twice? Is this some sort of redundancy thing I haven't heard about or have I got something configured incorrectly? Or am I misinterpreting the way table-spaces are handled? The tablespace was set up thusly: CREATE TABLESPACE ts_autodrs_main OWNER AutoDRS LOCATION 'D:/Database/AutoDRS/Data'; And the table in question defined as: create table job_classification ( dealer_id text not null , date_changed timestamp null , time_changed time null , job_id text not null , des text , user_id text null, access_reports_processed text ) WITHOUT OIDS TABLESPACE ts_autodrs_main ; Cheers, P. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds Reynolds Company ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Query re disk usage
Paul Lambert wrote: I note however, that the pg_database directory on C drive also grows at the same time to 116MB. That was meant to say the pg_tblspc directory. Both directories (my tablespace and pg_tablespace) contain the same set of files - same names and sizes, eg both contain a file 17177 with a size of around 58Mb and both contain another file 17179 with a size of around 53Mb - I'm assuming one being the data, one being the primary key index. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds Reynolds Company ---(end of broadcast)--- TIP 1: 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: [GENERAL] Query re disk usage
Magnus Hagander wrote: Sounds like the WAL log. It's in the pg_xlog directory - verify that that's where the data is increasing. The WAL log is global and not per-tablespace, so it doesn't follow your tablespaces location. Nope, it's the files in the pg_tblspc directory on my C drive, they are named identically to the files in my tablespace directory on D drive and have identical sizes. When I add data to a table in this tablespace, I see concurrent increases in file size in both my own tablespace directory and the pg_tblspc directory tree. Eg within this directory tree I have a file 17177 which represents my job_classification table, if I insert a bunch of records, the 17177 file on my D drive increases in size as does the 17177 file in the pg_tblspc subdirectory. I can see the pg_xlog files, which aren't an issue as they are archived off to E drive. I've just noticed in the tablespace documentation (Ch 19.6) that PG makes use of symbolic links that point to any user-defined tablespaces but AFAIK W2K3 doesn't support symlinks. -- Paul Lambert Database Administrator AutoLedgers - A Reynolds Reynolds Company ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Function to determine next payment date
I'm trying to create a function to determine the next due payment date
of a recurring expense.
I have a table containing the date the payment first started, and a
payment frequency key which relates to a payment ID in another table
containing a string defining how frequent the payment exists.
-- Cut down version of table definitions:
CREATE TABLE tblRecuringExpenses
(
expense_id integer NOT NULL DEFAULT nextval('seq_expense_id'::regclass),
expense_frequency character varying(1) NOT NULL,
expense_startdate timestamp with time zone NOT NULL,
CONSTRAINT pk_tblrecuringexpenses_expense_id PRIMARY KEY (expense_id),
CONSTRAINT fk_tblrecuringexpenses_expense_frequency FOREIGN KEY
(expense_frequency)
REFERENCES tblRecuringFrequency (frequency_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
CREATE TABLE tblRecuringFrequency
(
frequency_id character varying(1) NOT NULL,
next_payment character varying(40),
CONSTRAINT pk_tblrecuringfrequency_frequncy_id PRIMARY KEY (frequency_id)
)
WITHOUT OIDS;
I figure the best way to get the next payment date is to create a
function that takes in the payment ID, then loops through adding the
payment frequency interval onto the start date until it gets to a date
that's greater than or equal to the current date.
Firstly... is this assumption correct or is there an easier way to do it?
Secondly... assuming my initial assumption is correct I've created the
following function in PL/pgSQL, however when I execute it, it runs for
minutes - where the two tables above I'm working with are dummy test
tables that contain 2 rows each and the calculations are simple enough
that I would assume a speedy conclusion - it would seem to me that my
loop is endless.
An explain analyze does me no good - that get's stuck in the same
endless loop, and a simple explain only gives me:
Result (cost=0.00..0.01 rows=1 width=0)
CREATE OR REPLACE FUNCTION fnNextPaymentDue(integer) RETURNS timestamp AS $$
DECLARE
start_date timestamp;
next_payment interval;
loop_count int;
BEGIN
SELECT INTO start_date (SELECT expense_startdate FROM
tblRecuringExpenses WHERE expense_id=$1);
SELECT INTO next_payment (select next_payment from
tblRecuringFrequency JOIN tblRecuringExpenses on
(tblRecuringFrequency.frequency_id
=tblRecuringExpenses.expense_frequency)
WHERE expense_id=$1)::interval;
LOOP
if start_date =current_date then
exit;
end if;
start_date:=start_date+next_payment;
END LOOP;
RETURN start_date;
END;
$$ LANGUAGE PLPGSQL STABLE;
Since it's my first function in PL/pgSQL I've probably missed something
pretty obvious - but if someone could point it out to me that would be
muchly appreciated.
--
Paul Lambert
Database Administrator
AutoLedgers
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Function to determine next payment date
Paul Lambert wrote: I'm trying to create a function to determine the next due payment date of a recurring expense. I have a table containing the date the payment first started, and a payment frequency key which relates to a payment ID in another table containing a string defining how frequent the payment exists. Ignore this, I figured it out - had a column name in the table the same as the variable name I was using so it was getting mixed up I think. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 1: 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: [GENERAL] copy command - date
novice wrote: I'm using pg version 8.2.4. What is the best method to load this data? I have just a little over 55,000 entries. db5= \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: 3665 OK SM 07/07/13 06:09 CONTEXT: COPY maintenance, line 1, column maintenance_id: 3665 OK SM 07/07/13 06:09 That's not complaining about the date, that is complaining that your input file does not contain the maintenance_id column. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] copy command - date
Paul Lambert wrote: novice wrote: I'm using pg version 8.2.4. What is the best method to load this data? I have just a little over 55,000 entries. db5= \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: 3665 OK SM 07/07/13 06:09 CONTEXT: COPY maintenance, line 1, column maintenance_id: 3665 OK SM 07/07/13 06:09 That's not complaining about the date, that is complaining that your input file does not contain the maintenance_id column. I don't think copy allows you to leave columns out of your input file - even if they belong to a sequence. You could try something like: -- Create a temp table with everything but the sequence column. CREATE TABLE maintenance_load AS SELECT meter_id,status,inspector,inspection_date FROM maintenance WHERE 1=0; -- Copy data from file into temp table. COPY maintenance_load FROM 'd:/temp/file.txt'; -- Insert data from temp table into main table, which will -- generate the value for the sequence field. INSERT INTO maintenance (meter_id,status,inspector,inspection_date) (SELECT * from maintenance_load); -- Drop temp table. DROP TABLE maintenance_load; Also, not sure if it was your mail client or not, but the data you have supplied was space-separated, you probably want to make sure the actual data file is tab-separated, otherwise it's going to think it's all part of one field. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] TimestampTZ
Naz Gassiep wrote: As clearly stated in the documentation http://www.postgresql.org/docs/8.2/interactive/datatype-datetime.html#DATATYPE-TIMEZONES Perhaps I'm thick, but I don't find that particular page to be clear on this at all. - Naz. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Refer to this paragraph: (8.5.1.2) For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] TimestampTZ
Paul Lambert wrote: Refer to this paragraph: (8.5.1.2) For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone. Chapter 8.5.1.3 that was actaully, my apologies. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] WAL Queries
RPK wrote:
I installed PGSQL on Windows XP. I ran:
Select pg_start_backup('label');
It responded: Archive disabled
Is WAL archiving disabled by default?
Can this feature be enabled while installing PGSQL?
Can I change the path of the logs to another partition on my hard disk?
Enable it in the Write Ahead Log section of postgresql.conf file in
the PGDATA directory.
--
Paul Lambert
Database Administrator
AutoLedgers
---(end of broadcast)---
TIP 1: 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: [GENERAL] WAL Queries
RPK wrote: Before modifying postgresql.conf, do I need to stop PGSQL service? Enable it in the Write Ahead Log section of postgresql.conf file in the PGDATA directory. You need to restart PostgreSQL after the file has been modified - it does not need to be shut down whilst the modification is being undertaken, just a quick restart once you have saved the file. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] WAL Queries
RPK wrote: How to change the path of WAL? How much free space to leave on hard disk for effective performance? Since it is disabled by default, is it insignificant to ENABLE it? Just change the settings under the WAL section of the config file. The WAL files themselves will end up in the pg_xlog directory in pgdata, but you can define the archive_command setting to archive the files off to another directory. Refer to the WAL section of the manual for details on what to set the settings to. http://www.postgresql.org/docs/8.2/interactive/wal-configuration.html How much hard-disk space you need is largely dependant on the size of your database and the size and frequency of updates coming through. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Changing column types
Owing to a problem with the way access and a couple of other programs I've found handle text columns in PG as something they call 'memo', I want to change all of the text columns in my database to varchar. There's about 600 text columns all up, so I'm looking for a quick way of doing this. Is there any way, using the system catalogues - pg_tables, pg_attributes etc that I can automatically change all text type fields to varchar or am I better of just recreating the schema? -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Changing column types
Paul Lambert wrote: Owing to a problem with the way access and a couple of other programs I've found handle text columns in PG as something they call 'memo', I want to change all of the text columns in my database to varchar. There's about 600 text columns all up, so I'm looking for a quick way of doing this. Is there any way, using the system catalogues - pg_tables, pg_attributes etc that I can automatically change all text type fields to varchar or am I better of just recreating the schema? Ignore this message, I've found a solution on the ODBC side. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] varchar(n) VS text
Tom Lane wrote:
Pierre Thibaudeau [EMAIL PROTECTED] writes:
I am puzzling over this issue:
1) Is there ever ANY reason to prefer varchar(n) to text as a column type?
In words of one syllable: no.
Not unless you have an application requirement for a specific maximum
length limit (eg, your client code will crash if fed a string longer
than 256 bytes, or there's a genuine data-validity constraint that you
can enforce this way).
Or if you want to have schema-level portability to some other DB that
understands varchar(N) but not text. (varchar(N) is SQL-standard,
while text isn't, so I'm sure there are some such out there.)
From my reading of the dataype documentation, the ONLY reason I can
think of for using varchar(n) would be in order to add an extra
data-type constraint to the column.
That is *exactly* what it does. No more and no less. There's no
performance advantage, in fact you can expect to lose a few cycles
to the constraint check.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Is there any disk space advantages to using varchar over text? Or will a
text field only ever use up as much data as it needs.
I have a database where pretty much all text-type fields are created as
varchars - I inherited this db from an MS SQL server and left them as
varchar when I converted the database over to PG. My thoughts were text
being a non-constrained data type may use up more disk space than a
varchar and if I know there will never be more than 3 characters in the
field for example, I could save some space by only creating a 3 length
field.
In my case, any field length restrictions are governed by the
application so I don't really need the constraint built into the back
end. If there is a slight performance disadvantage to using varchar and
no real disk space saving - and I have in some cases 40 or 50 of these
fields in a table - then would it be better for me to convert these
fields to text?.
Not to mention that I run into a problem occasionally where inputting a
string that contains an apostraphe - PG behaves differently if it is a
varchar to if it is a text type and my app occasionally fails.
I.e.
insert into tester (test_varchar) values ('abc''test');
I get the following:
ERROR: array value must start with { or dimension information
SQL state: 22P02
If I use the same command but inserting into a text-type field.
insert into tester (test_text) values ('abc''test');
It works fine.
But that's beside the point - my question is should I convert everything
to text fields and, if so, is there any easy way of writting a script to
change all varchar fields to text?
--
Paul Lambert
Database Administrator
AutoLedgers
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] varchar(n) VS text
Michael Glaesemann wrote:
Works for me:
test=# select version();
version
--
PostgreSQL 8.2.4 on powerpc-apple-darwin8.9.0, compiled by GCC
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build
5367)
(1 row)
test=# create table tester (test_varchar varchar primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
tester_pkey for table tester
CREATE TABLE
test=# insert into tester (test_varchar) values ('abc''test');
INSERT 0 1
test=# select * from tester;
test_varchar
--
abc'test
(1 row)
Michael Glaesemann
grzm seespotcode net
Looks like my bad - I created the table initially through pgAdminIII and
it appears I selected the wrong character varying from the dropdown list.
CREATE TABLE tester
(
test_varchar character varying[],
test_text text
)
If I change it to character varying(20) it works fine.
Apologies for that.
Thanks for the other info though.
--
Paul Lambert
Database Administrator
AutoLedgers
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Windows Vista Support
[EMAIL PROTECTED] wrote: Can you confirm that you don't provide support for Windows Vista for any release of Postgres. I'm dumbfounded an it appears that you don't support Vista. If so, are you planning any releases. I have a major project and was hoping to use Postgres. Michael Alexander Impower I've got PG running on a few Vista machines at this stage without problems. The main thing you need to look out for is disabling User Account Control for the installation process, otherwise the install will fail. This is done through Control Panel - User Accounts. Once the install is completed you can re-enable it without issue. Regards, -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Fault Tolerant Postgresql (two machines, two postmasters, one disk array)
Ron Johnson wrote: Dinosaurist? The big systems we use were last upgraded 5ish years ago, and are scheduled (eventually) to be replaced with Oracle on Linux. We've got some pretty new Alpha servers (around a year old) running VMS 8.3 which was released about the same time we got the servers...or shortly before. Sure it's been around nearly since the dawn of time, but it's still an actively developed operating system. I've finally got my Alpha server at home up and running now too, and I hope to be getting PG running on it as part of my thesis project when I start that in the near future, if my schedule allows. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Fault Tolerant Postgresql (two machines, two postmasters, one disk array)
Ron Johnson wrote: On 05/12/07 01:51, Paul Lambert wrote: Sure it's been around nearly since the dawn of time, but it's still an actively developed operating system. I've finally got my Alpha server at home up and running now too, and I What are you running? Off hand I couldn't tell you - It's a Compaq Alphastation model - so hardware wise my home server is a few years old, it's got 2*18Gb SCSI disks and a 555MHz processor if memory serves me correct with a gig of ram. Currently running OpenVMS 7.3-2 but I'll be upgrading to 8.2 or 8.3 shortly. I can get more accurate specs next time I'm home and can be bothered booting the machine up... it doesn't have much more than what I've already listed though - CPU speed is the only thing I'm not 100% on. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] backup and restore
anhtin wrote: Anybody show for me ? i want backup database and i read on Internet have function pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup) but i not run Some body show me. How will i do run this function ?? See: http://www.postgresql.org/docs/8.2/static/continuous-archiving.html -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[Fwd: Re: [GENERAL] backup and restore]
anhtin wrote: Anybody show for me ? i want backup database and i read on Internet have function pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup) but i not run Some body show me. How will i do run this function ?? See: http://www.postgresql.org/docs/8.2/static/continuous-archiving.html Are you trying to use PG to backup an MS SQL server database? -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Fault Tolerant Postgresql (two machines, two postmasters, one disk array)
Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/11/07 08:31, Geoffrey wrote: Call me elitist, but I've used OpenVMS for so long that if it's not a VMS-style shared-disk cluster, it's a false usage of the word. Compute-clusters excluded, of course. Hear here! (I guess I'm elitist too) :) - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGRHbXS9HxQb37XmcRAg04AKC5btWR3CVebNM2HbMQG+6IeiSZqQCfRMst RkulQKSefuR04O6D/3xlbaY= =7cNv -END PGP SIGNATURE- -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Idle session timeout?
Sean Murphy wrote: Scott Marlowe wrote: On Tue, 2007-05-08 at 15:59, Sean Murphy wrote: Scott Marlowe wrote: On Tue, 2007-05-08 at 12:19, Sean Murphy wrote: Tom Lane wrote: Sean Murphy [EMAIL PROTECTED] writes: I'm WAY out of my depth here, but my impression, based on the circumstances, is that there is some sort of an idle session timeout kicking in (most likely on the client side) and dropping the connection. There's no such timeout in the Postgres server, for sure. I would actually bet that your problem is in some router between the client and the server. In particular, routers that do NAT address mapping typically have a timeout after which they will forget the mapping for an idle connection. If you've got one of those, see if it'll let you change the timeout. If you can't do that, you might think about teaching your client-side code to send dummy queries every so often. regards, tom lane I've already maxed out the connection timeout at the firewall... and I've been using dummy queries every five minutes, but it just feels like a crutch to do so. Have you looked into tcp keepalive settings? net.ipv4.tcp_keepalive_intvl = 75 net.ipv4.tcp_keepalive_probes = 9 net.ipv4.tcp_keepalive_time = 500 Not sure if those settings will help with an NAT router or not but it's worth a try. Pgsql 8.2 can set those for you. I may be celebrating prematurely, Never stopped me :) but resetting the tcp_keepalive parameters seems to have done the trick - I left a pgAdmin connection that *always* drops after inactivity up while I went to lunch and it was still alive when I got back. Is there a way to alter the tcp_keepalive settings on an app-by-app basis rather than for the whole system? Well, you could set it on individual workstations instead of on the server. I.e. if you set tcp_keepalive on your workstation to 500, but leave Wally and Dilbert set at the default 7200 then they'd still timeout and you wouldn't. Unfortunately, my individual workstations are all running Windows...:( any idea if/where this could be set in MS-land? You need to alter some settings in the registry. See: http://msdn2.microsoft.com/en-us/library/aa302363.aspx -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Vacuuming
Is there any point to vacuuming a table if it has been bulk-populated by data after a truncate? I.e. If I do this: TRUNCATE TABLE vehicles; INSERT INTO vehicles (SELECT DISTINCT ON (dealer_id,vehicle_address) * FROM vehicles_temp_load WHERE (dealer_id,vehicle_address) is not null); Is there any point in vacuuming? Also, is there any point in recreating indexes on this table after a load like this or will indexes have been correctly maintained/updated by the above insert. Note: This insert often loads tens of millions of records. BTW, this is on Windows. Thanks, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Vacuuming
Tom Lane wrote: The only thing a vacuum would do for you there is set the commit hint bits on the newly-inserted rows. Which might be worth doing if you want to get the table into a totally clean state, but it's probably a bit excessive. SELECTs on the table will set the hint bits anyway as they visit not-yet-hinted rows, so it's really a matter of do you want to pay that overhead all at once or spread-out. What you *do* want to do in this situation is an ANALYZE. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ Thanks Tom. Should the ANALYZE be done before or after indexes are built? Or is that irrelevant? Should I not even bother rebuilding indexes when I do these loads? Currently I: 1) Drop Indexes 2) Truncate and copy in new data 3) Vacuum - now changed to analyze. 4) Create indexes I add steps one and four on the assumption that adding 40 million records in one hit might get the indexes confused - but if they are pretty stable I can remove these steps. P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Vacuuming
Tom Lane wrote: I forgot to mention that any other operation that examines every table row will fix all the hint bits as well. In particular a CREATE INDEX would do that --- so if you are planning to create some indexes then there's certainly no point in a VACUUM just after a table load. regards, tom lane Thanks for all the help Tom, educational as always. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] PG Books
I've come across a couple of books on PG that appear interesting and was wondering if others have read them and what their thoughts are. (Excuse me if this topic has already been covered previously) Practical PostgreSQL - Command Prompt PostgreSQL - Bruce Momjian I know Bruce and the CP folks are regular posters here and I am certainly aware of their in depth knowledge on PG so as far as knowing that the books would be reliable in the information they give I have no problems there. At the moment my usage of PG is fairly basic, a bunch of tables that contains a replication of data in our primary application which is loaded by a single application and used by our customers with MS Access, Crystal Reports and other tools to generate reports. I.e. in effect to our customers it's a read only database and any data being put in has already been subjected to constraint testing etc by the primary (non PG) database so I haven't set up any foreign keys, triggers/functions, views, sequences etc. Really the only thing in use is basic data in tables with some indexes to make reports quicker. I want to start expanding the usage and getting into the guts of proper performance tuning (right now all PG installations are using the default parameters - the only thing that has been changed is the pg_hba file to allow access) and these books look like they might be worth reading to understand a bit more about how all this works. Can anyone who has read the books let me know if you have found them to be valuable reference tools for someone who is relatively novice at PG (but not at databases in general) - Note: Praise from the books authors will be considered marginally bias and therefore untrustworthy :) I know, I could have written a two line email asking this question, but sometimes I like to go on and on and on and on. Thanks for your input though. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PG Books
Listmail wrote: loaded by a single application and used by our customers with MS Access, From by previous job where we had an Access based backoffice application, you might want to learn enough to be ready to switch to something better (ie postgres) when you feel the need. I think you misunderstand what I was describing. We do use Postgres, our customers use Access as a report generating tool running off the PG backend. That part I'm not concerned with, we merely supply the database - which yes was in SQL Server, but I migrated it to PG late last year. Access is a booby trap setup by Microsoft to sell SQL server. It will work well up to a certain point, then die miserably (some of our web pages were taking minutes to load !). When this happens many people complain and you usually buy SQL server to ease the pain. Or, as we did, you ditch the MS stuff and switch to other tools. I wasn't in charge of this, so can't say more, but beware of Access. We leave it up to out customers to decide what reporting tool to use, most use access for its simple report generation, some use Crystal Reports, others build web pages that interface to the backend database. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- Paul Lambert Technical Support Team Leader and Database Administrator AutoLedgers Level 3, 823 Wellington Street, West Perth, W.A. 6005 Postal: P.O. Box 106, West Perth, W.A. 6872 Ph: 08 9217 5086 Fax: 08 9217 5055 AutoLedgers Technical Support Desk: 1800 649 987 (Free call) 08 9217 5050 (Perth local and mobile) Email: [EMAIL PROTECTED] http://www.reynolds.com.au For AutoLedgers technical support, please send an email to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PG Books
Guillaume Lelarge wrote: Paul Lambert a écrit : I've come across a couple of books on PG that appear interesting and was wondering if others have read them and what their thoughts are. (Excuse me if this topic has already been covered previously) Practical PostgreSQL - Command Prompt PostgreSQL - Bruce Momjian [...] Can anyone who has read the books let me know if you have found them to be valuable reference tools for someone who is relatively novice at PG (but not at databases in general) - Note: Praise from the books authors will be considered marginally bias and therefore untrustworthy :) I've read both of them and found them really interesting 3 or 4 years ago. Sadly, they are quite old (PostgreSQL: Introduction and Concepts published in 2000, 2002 for Practical PostgreSQL). You may better look at this list : http://www.postgresql.org/docs/books/ Beginning Databases with PostgreSQL is a really good intro. Regards. Ahh, thanks for that link, I'll check them out :) -- Paul Lambert Technical Support Team Leader and Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Using MS Access front-end with PG
I've got an MS Access front end reporting system that has previously
used MS SQL server which I am moving to Postgres.
The front end has several hundred if not thousand inbuilt/hard-coded
queries, most of which aren't working for the following reasons:
1.) Access uses double quotes () as text qualifiers, PG uses single
quotes. ('')
2.) The Like function in SQL Server is case insensitive, PG it is case
sensitive. The ilike function is not recognised by Access and it tries
to turn that into a string, making my test (like ilike 'blah')
Has anyone had any experience with moving an access program from SQL
server to PG?
Is there any way to change the text qualifier in PG or the case sensitivity?
TIA,
P.
--
Paul Lambert
Database Administrator
AutoLedgers
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Using MS Access front-end with PG]
Tom Lane wrote: Paul Lambert [EMAIL PROTECTED] writes: Is there any way to change the text qualifier in PG No. I suppose you could hack the Postgres lexer but you'd break pretty much absolutely everything other than your Access code. or the case sensitivity? That could be attacked in a few ways, depending on whether you want all text comparisons to be case-insensitive or only some (and if so which some). But it sounds like MS SQL's backward standards for strings vs identifiers has got you nicely locked in, as intended :-( so there may be no point in discussing further. I don't have any case sensitive data - so if sensitivity could be completely disabled by a parameter somewhere, that would be nice. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Using MS Access front-end with PG]
Joshua D. Drake wrote:
You could preface all your queries with something like:
select * from foo where lower(bar) = lower('qualifer');
But that seems a bit silly.
Joshua D. Drake
I'm trying to avoid having to alter all of my queries, per the OP I've
got several hundred if not thousands of them and if I have to change
them all to put lower() around all the text, that is a lot of time.
If I have to do that I will, I'm just curious if there was an ability to
tell pg to not be case sensitive when doing lookups.
Judging by the responses so far, there is not... so I'll get to work :)
--
Paul Lambert
Database Administrator
AutoLedgers
---(end of broadcast)---
TIP 1: 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: [GENERAL] Using MS Access front-end with PG
Paul Lambert wrote:
I've got an MS Access front end reporting system that has previously
used MS SQL server which I am moving to Postgres.
The front end has several hundred if not thousand inbuilt/hard-coded
queries, most of which aren't working for the following reasons:
1.) Access uses double quotes () as text qualifiers, PG uses single
quotes. ('')
Ignore point one in my op, it wasn't the double quotes causing the
problem and was a quick and easy fix.
Thanks,
P.
--
Paul Lambert
Database Administrator
AutoLedgers
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PgSql on Vista?
Arkan wrote: Hi all, have anybody installed PgSQL on Windows Vista? I tried yesterday but I fail... on XP and linux i've installed pgsql much times but on vista... nothing to do! Version in 8.2.3 with the installer. If I install with my user account (with administrative rights), the installlation fails on setting file permission, about at 3/4 of the process. If I install with Administrator account, the installer fail when starting the service.. any ideas? I've got it running on my vista machine, you just need to turn off User Account Control from the Control Panel - Security Settings - Other Settings for the duration of the installation. After install completes you can turn it back on... if you want - personally I leave it off, it's an incredibly annoying feature. Note, turning UAC off and on both require reboots. P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PgSql on Vista?
Dave Page wrote: Paul Lambert wrote: After install completes you can turn it back on... if you want - personally I leave it off, it's an incredibly annoying feature. Doesn't the security center keep popping up to point out that it's turned off? Regards Dave Ahh, but there is an option on the menu on the lefthand side of security center Change the way security center alerts me through which you can select to either have an icon in your taskbar with a popup, to have just an icon in your taskbar, or to do nothing. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 1: 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: [GENERAL] pg_dumpall and version confusion
Porting it to Windows could be tricky though, given its use of symlinks and wrapper scripts. FWIW, as of Vista and Server Longhorn, Windows now supports Symlinks. I don't know about wrapper scripts though. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PgSql on Vista?
Alvaro Herrera wrote: Dave Page escribió: Paul Lambert wrote: After install completes you can turn it back on... if you want - personally I leave it off, it's an incredibly annoying feature. Doesn't the security center keep popping up to point out that it's turned off? You mean, like this? http://images.apple.com/movies/us/apple/getamac/apple-getamac-security_480x376.mov Hillarious :) Yet sadly accurate... -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 1: 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: [GENERAL] PG periodic Error on W2K
Magnus Hagander wrote: On Thu, Mar 01, 2007 at 10:45:16AM -0500, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: On Thu, Mar 01, 2007 at 09:44:19AM +0900, Paul Lambert wrote: I am periodically getting errors pop up on the server console of the following nature: The File or directory D:\PostgresQL\Data\global\pgstat.stat is corrupt and unreadable. Please run the Chkdsk utility. They can *not* be caused by a bug in PostgreSQL - no more than a kernel oops in linux is the fault of PostgreSQL. Now, we do push the filesystem and disk layer in an unusual way with the pgstats writes, gievn that we rewrite the same file over and over and over and over again at very short intervals. But nothing says we're not allowed to do that :-) I'm wondering whether the message is coming from the kernel, or some sort of file-scanning utility that gets confused when a file is deleted while it's looking at it. That specific message comes from the kernel. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Still getting the errors, but I managed to convince the powers that be to order me a fancy new server so we'll see how that goes when it arrives. Thanks again to all for the info though. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PG periodic Error on W2K
[EMAIL PROTECTED] wrote: Date: Thu, 01 Mar 2007 10:06:44 +0900 From: Paul Lambert [EMAIL PROTECTED] To: Joshua D. Drake [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: Re: PG periodic Error on W2K Message-ID: [EMAIL PROTECTED] I propound to all my sincerest of apologies for installing what I believe to be a marvel of human creation in Postgres on what most believe to be the ultimate travesty of what some claim to be an operating system in Macrohard Webloze. [text excluded here] I would be delighted to offer my assurance that such a farce would never again take place, but I have about forty such installations to do when my development is complete :( Paul Lambert Hi Paul, Have you thought of running the Postgresql on a Linux box and then developing your application to use the Postgresql server via ODBC? I am in a Windows environment on a Windows / Novell network. We still use Novell for log-on and security management. I develop in Visual FoxPro because the IT head wants to keep us on Windows clients but he agreed to let me try developing with a Linux server. I have been working with Postgresql on a Redhat 9 server for a couple years now and it is much more stable (and more secure) than our SQLServer databases on a Windows 2000 server. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 I've put thought of running it on Linux... a lot of thought... but I haven't for 2 reasons. 1.) I've had very limited exposure to Linux, most of my work is done in an OpenVMS environment with some in Weendoze, I wouldn't feel comfortable configuring a Linux environment let alone configuring it properly. 2.) All of our customers run Weenblows servers and also don't have the expertise to maintain a linux server, it's a lot easier for us to sell our product to them if we can just install it on their existing server and have them not worry about upgrading or adding anything to their server room. Can I put in another plug for an OpenVMS port? Or would I have to do the work myself? ;) -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Thanks to all
Thanks to all who have helped me over the last month or so with converting my system from M$ SQL server to Postgres. Unfortunately I've decided to scrap the project and continue working with M$ SQL Server... PG just isn't doing what I want. No... I jest, I've finally got the entire system up and running exactly as I want it and I am ready to do my first customer install. That's another 40 or 50 PG installs over the next few months :D A big thankyou to everyone who has given me advise up to now... and a big thanks in advance to those who will continue to advise me in the future ;) Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PG periodic Error on W2K
I'm running PG 8.2.3 on We doze 2000 Server. (Should I apologise for that up front to appease the masses?) I am periodically getting errors pop up on the server console of the following nature: The File or directory D:\PostgresQL\Data\global\pgstat.stat is corrupt and unreadable. Please run the Chkdsk utility. and The file or directory D: is corrupt and unreadable. Please run the Chkdsk utility. Now, per the errors suggestion I have run the chkdsk utility with a /X /F switch to do a complete check on reboot before mounting the volume. This showed no errors. I can also open the mentioned file - pgstat.stat - using notepad or any other program without mention of corruption and the data within the file looks to be uniform suggesting it is fine. Strangely enough, this error was being presented on the last server I had it running on, and was in fact one of the reasons I moved it - I assumed the error was due to dodgy disks but this seems a bit much of a coincidence. I know these errors are not coming directly from Postgres, but does anyone else have problems (or has had previously) of a similar nature or any suggestions on where it may be? As a side-note, this server is RAID controlled, the D drive has 3 disks in the array - I would therefore have assumed that if there was a problem with one of the disks then the server would carry on using the other disks. I can find no performance degradation in Postgres, the service and connections et al. keep on operating as though there was nothing wrong, but the errors continue to pop up sporadically on the console. Thoughts? Ideas? Suggestions? Should I bugger off? -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PG periodic Error on W2K
Joshua D. Drake wrote: Paul Lambert wrote: I'm running PG 8.2.3 on We doze 2000 Server. (Should I apologise for that up front to appease the masses?) Probably ;) I propound to all my sincerest of apologies for installing what I believe to be a marvel of human creation in Postgres on what most believe to be the ultimate travesty of what some claim to be an operating system in Macrohard Webloze. I would be delighted to offer my assurance that such a farce would never again take place, but I have about forty such installations to do when my development is complete :( I am periodically getting errors pop up on the server console of the following nature: This showed no errors. I can also open the mentioned file - pgstat.stat - using notepad or any other program without mention of corruption and the data within the file looks to be uniform suggesting it is fine. Try turning off stats. However you will need to run vacuum using some other method. Joshua D. Drake Thoughts? Ideas? Suggestions? Should I bugger off? The Windows port is still young, we are here to help. Try the above and see if the problem goes away :) Sincerely, Joshua D. Drake I should mention that this particular database is my development server so at this point it doesn't have any data because I'm still trying to get the program prepared that populates is. (All tables have zero rows) I don't think vacuuming will do much for me anyway so I have no problems if I can't run it :) I'll turn off stats and see what happens. Thanks. -- Paul Lambert Technical Support Team Leader and Database Administrator AutoLedgers Level 3, 823 Wellington Street, West Perth, W.A. 6005 Postal: P.O. Box 106, West Perth, W.A. 6872 Ph: 08 9217 5086 Fax: 08 9217 5055 AutoLedgers Technical Support Desk: 1800 649 987 (Free call) 08 9217 5050 (Perth local and mobile) Email: [EMAIL PROTECTED] http://www.reynolds.com.au For AutoLedgers technical support, please send an email to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Error upgrading on W2K
I have postgres running on W2K, version 8.2.1 which I am upgrading to 8.2.3 but when I run the upgrade I get an error as follows: The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2803. The install seemed to continue fine after this point and when I check in psql I have 8.2.3 installed and the databases appear to be operational. Any thoughts on what the error might have been and if I need to check anything in particular to verify correct install? Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Setting up functions in psql.
Tomas Vondra wrote: AutoDRS=# select fnLoadAppraisals(); ERROR: relation with OID 18072 does not exist CONTEXT: SQL function fnLoadAppraisals statement 5 18072 is the OID of table appraisals_temp_load If I run the code within the function by itself, i.e. copy and paste the 6 lines of SQL int psql it runs fine... What precisely is this error telling me? It's not entirely clear to me. This is caused by the fact that the function remembers OIDs once it's parsed. So once it reaches the COPY, the original table (with the OID 18072) does not exist (the new table has a different one). This is a feature, not a bug! You can bypass this using dynamic SQL, ie. use EXECUTE 'DROP ...'; EXECUTE 'CREATE ...'; instead of plain DROP / CREATE. Dynamic SQL could be a performance issue in some cases (as the query has to be parsed each time it's executed) but this probably is not the case. Tomas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings I've got 35 tables that need to be reloaded in this way and I'd rather not have to leave 35 extra tables lying around, (per someone else's suggestion of leaving them there) I'll give execute a try on Monday when I'm back in work and see if that solves my problems. These functions will only need to be run once every six to nine months (if even that often) and will be done whilst database access is removed so performance is not a problem during the loading process. Cheers for the pointer. P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ROLE INHERIT
David Legault wrote: Hello, I'm a bit new to Postgre, and I'm experimenting with the roles stuff. I want to know why If I create a role called administrator (a group basically, no login) : CREATE ROLE administrator NOSUPERUSER INHERIT NOCREATEDB CREATEROLE; And then create a user CREATE ROLE admin LOGIN PASSWORD 'password' ON ROLE administrator; admin doesn't have the CREATEROLE privilege himself, but because he is part of a group that has it, why doesn't this fall back on him having it? When I try to use that admin user to create another role, it says insufficient privileges. Am I missing something in this role stuff ? Thanks David Doesn't the inherit property need to be on the role that will do the inheriting? I.e. if admin is to inherit the privileges of administrator, then admin needs the inherit property. A role with the INHERIT attribute can automatically use whatever database privileges have been granted to all roles it is directly or indirectly a member of. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Setting up functions in psql.
In setting up some functions to load data from a csv file, I'm doing the following in psql on Weendoze: AutoDRS=# CREATE OR REPLACE FUNCTION fnLoadAppraisals() AutoDRS-# RETURNS void AS AutoDRS-# $BODY$ AutoDRS$# DROP TABLE IF EXISTS appraisals_temp_load; AutoDRS$# CREATE TABLE appraisals_temp_load AS SELECT * FROM appraisals WHERE 1=0; AutoDRS$# TRUNCATE TABLE appraisals; AutoDRS$# COPY appraisals_temp_load FROM 'c:/temp/autodrs_appraisal.txt' WITH DELIMITER AS '^' CSV HEADER; AutoDRS$# INSERT INTO appraisals (SELECT DISTINCT ON (dealer_id,appraisal_id) * FROM appraisals_temp_load); AutoDRS$# DROP TABLE IF EXISTS appraisals_temp_load; AutoDRS$# $BODY$ AutoDRS-# LANGUAGE 'sql' VOLATILE; ERROR: relation appraisals_temp_load does not exist CONTEXT: SQL function fnLoadAppraisals AutoDRS=# ALTER FUNCTION fnLoadAppraisals() OWNER TO AutoDRS; ERROR: function fnLoadAppraisals() does not exist I can see why the error occurs, the table appraisals_temp_load is being created and then deleted - I don't leave it in the database. What I am confused about is: Why does the creation of a function fail if a table it uses does not exist when the function itself is creating the table further up to where it references it? Should I be doing this in a different way? (Yes I know it's easy enough to just create the table before creating the function, I'm just curious as to why it should fail) Secondly, and here's the obviously easy one that I'm having a mental blank trying to figure out... How would I execute a function (such as the above) from psql? -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Setting up functions in psql.
Tom Lane wrote: Paul Lambert [EMAIL PROTECTED] writes: What I am confused about is: Why does the creation of a function fail if a table it uses does not exist when the function itself is creating the table further up to where it references it? Because the function isn't actually being *executed*, only syntax-checked. The syntax precheck isn't completely reliable, for this reason among others, so you can turn it off via check_function_bodies = off. However, I'm not sure but what the function would fail anyway at runtime for the same reason. I think in a SQL function, it all gets parsed before any is executed. (This could probably get fixed, if we thought it was worth the trouble.) Secondly, and here's the obviously easy one that I'm having a mental blank trying to figure out... How would I execute a function (such as the above) from psql? select fnLoadAppraisals(); regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend AutoDRS=# select fnLoadAppraisals(); ERROR: relation with OID 18072 does not exist CONTEXT: SQL function fnLoadAppraisals statement 5 18072 is the OID of table appraisals_temp_load If I run the code within the function by itself, i.e. copy and paste the 6 lines of SQL int psql it runs fine... What precisely is this error telling me? It's not entirely clear to me. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to search, how to post?
Vladimir Zelinski wrote: Hi, I just now subscribed the mailing list, but I can't understand what I should do next. I need: 1) search forums for specific keywords http://archives.postgresql.org/ 2) be able to post my question. You just did... Sending mail to pgsql-list name@postgresql.org is all you need to do. Where list name is substituted with the list you want to post to (i.e. in the case of this one, pgsql-general@postgresql.org) How can I do that? I read help but it didn't have any information for helping me. Thank you, Vladimir ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PGSQL 8.2.3 Installation problem
marcelo Cortez wrote: Magnus I have NTFS only , i don't have FAT partitions at all. But the problem is not resolved. The install fail at create cluster for me. I set all permisions for user postgres. Binary manual installation .. make sense i try . It has any manual/instructions/links for that? Best regards MDC Have you run the setup with the 'write detailed installation log to postgresql-8.2.log in the current directory' checked? Can you paste the relevant bits of that log (which you can find in the same directory as the installation file) into a message so more educated persons can take a look? If it's a file system problem, I'd also suggest going to http://www.sysinternals.com to their file and disk utilities, download ntfilemon and run it to monitor file activity (HINT: Set the filter to include only *postgres*, otherwise you will be flooded with information relating to file system access from everything else on your server - and turn on advanced output) that may show you more information about what the error was from the file system point of view. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Stored Procedure examples
Walter Vaughan wrote: This may not help, but I noticed using pgAdminIII, you can create a procedure or a function, but they seem to have the same creation interface and use the same icon. A procedure is a function that returns null. You'll note if you create a procedure under pgAdminIII, it gets saved under functions, not under procedures. I spent a couple of minutes a few days back trying to find all the procedures I'd just created only to note that they were in fact sitting under functions. I couldn't see them under procedures, but when I tried to create them again I was told they already existed... it was hair pulling stuff there for a couple of minutes. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Installing on weendoze vista.
Has anyone had any success installing on weendoze vista? Any install I try gets as far as the service user details, if I ask it to create a user it fails, if I specify an existing user account it complains about the user not having enough access - even when said user account is put into the administrator group. I'm assuming PG hasn't been certified under vista yet? If this is correct, is there any plan to do so? Has anyone tried it under server longhorn? -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stored Procedure examples
Paul Lambert wrote: Walter Vaughan wrote: This may not help, but I noticed using pgAdminIII, you can create a procedure or a function, but they seem to have the same creation interface and use the same icon. A procedure is a function that returns null. That should have said void of course. You'll note if you create a procedure under pgAdminIII, it gets saved under functions, not under procedures. I spent a couple of minutes a few days back trying to find all the procedures I'd just created only to note that they were in fact sitting under functions. I couldn't see them under procedures, but when I tried to create them again I was told they already existed... it was hair pulling stuff there for a couple of minutes. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to create an archive for old records?
Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 19:11, carter ck wrote: Hi all, I am looking for ways to create an archive of records older than 3 months in one of my table, and store these extracted records into a local database. Does Postgres have any command to do this? A single command that will copy data to a destination database, and then delete from the source database? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF07V5S9HxQb37XmcRAmcmAKCBRmyMFbhnfC04VUwI29pUDEVpzgCdGeDi ZqGkW48PU/99qt9bs0waftA= =V7r+ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match I think the problem may be in determining when a record was added to the table. If there is no 'date added' column as part of your table specification that you populate when adding a row then is there any way to determine when a record was added? If there is a date added (which is a standard I put in all tables I use) then it should be a fairly straight forward task of doing an INSERT INTO followed by a DELETE FROM. As for a single command... I'm not aware of any INSERT INTO AND DELETE ORIGINAL variant so it would have to be two SQL commands, albeit it uncomplicated. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PGSQL 8.2.3 Installation problem
marcelo Cortez wrote: hi there same things occurs to me. Any body install win32 version with success??? best regards MDC --- RPK [EMAIL PROTECTED] escribió: When I run the setup of PGSQL 8.2.3, it displays error while initializing database cluster. Error displayed is: Failed to execute initdb. Unable to set file system permissions. I am installing on Windows XP SP2 with administrator log in. -- View this message in context: http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8947083 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq I've installed it on my WinXP Professional SP2 (32 bit) machine without error. Action start 6:33:07: SetPermissions. 1: Setting filesystem permissions... Action ended 6:33:07: SetPermissions. Return value 1. MSI (s) (F4:44) [06:33:07:312]: Doing action: RunInitdb Action 6:33:07: RunInitdb. Initializing database cluster (this may take a minute or two)... Action start 6:33:07: RunInitdb. 1: Initializing database cluster (this may take a minute or two)... Action ended 6:33:07: RunInitdb. Return value 1. I wasn't installing under administrator, did this under my own account and had the install create the 'postgres' user account. Perhaps something wrong with the default file permissions where you are installing Postgres. I assume Postgres creates directories that inherit the parent directory permissions. If you have given the parent restricted access, the 'postgres' user that PG runs under may not have access to those dirs. Only thing I can think of anyway, if not then I am not sure why you would be having a problem. Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Footprints on Postgres
Our help desk uses a job tracking system called Footprints, developed by Unipress (now Numara Software) which I have discovered this morning can run on Postgres. Does anyone on this list have any experience with this software, in particular using it with Postgres as it's backend. We currently use MS SQL server, and Footprints now remains as the only application in our organisation still relying on SQL server which I would like to get rid of - for reasons that would be obvious to even the most dim-witted in-duh-vidual. If anyone has experience running Footprints on Postgres in a Weenblows environment I wouldn't mind hearing from you on your experiences. Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Postgres training down under.
Are there any companies in the great land of Australia that offer Postgres training courses? I see a number listed for around the US on the postgresql.org website - just curious if anything similar goes on down under. Cheers, P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to allow users to log on only from my application not from pgadmin
Bruno Wolff III wrote: On Thu, Feb 01, 2007 at 10:24:51 +0900, Paul Lambert [EMAIL PROTECTED] wrote: If you hide the database username and password within your application (i.e. encrypted within the source code) so they cannot see the credentials that you connect to the database with internally then they have no means by which to connect to it using any other programs. This is not real security. Encrypting the data in the application only works if the application is running on a computer you control. If the customer can get their own copy of the client and run it on a computer they control then they can steal or borrow the applications credentials. How? If it is encrypted within the source code then the only way to steal the credentials would be to reverse engineer the application. And if someone is going to do that then you can be relatively assured that they are going to do anything and everything to get around whatever other security you can offer. At which point you could send the law after them for breach of copyright or other such law - at least that is the case down here in Australia. We have an application which connects to a database in MySQL. Each user has their own username/password to log onto the application which does so through authenticating against a users table in the db. The application itself has hard-coded within a username/password to get the initial access to the database. With somewhere in the vicinity of 1,000 people using this particular application we've not seen a case of anyone accessing it using anything other than our application. You want to either run the app on a computer you control It's not always feasible to host the application main on your own server. Depending on network distance, traffic, size of application, number of users etc, it could require some extremely high spec hardware to host and beefed up network connections. This is not possible for a lot of service providers out there, not to mention that those willing to reverse engineer the software (or run packet sniffers and decrypt network traffic) to get the password out of it would still find a way of determining the password your hosted app is using. or have a contract with the customers prohibiting them from connecting to the database other than by using the app. If customers access a database hosted by a service provider it is generally the norm to have some clauses in the contract pertaining to data protection and ownership making access to provider hosted data by any means other than those authorised by the provider a breach of contract. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq If security is as big a concern as it appears to be for Andrus then I would suggest that a bigger approach involving a number of security steps need to be taken. The password used to connect to the db initially is just the first. My standard practice however is to never allow the users to see the password that they connect to the db with. Options as I see it: 1.) Hard-code the password (perhaps in encrypted form) within the source of the application and do not supply that password to anyone else. This app knows how to connect to the db, and then data access is determined by credentials supplied by the user that authenticate against a users table in the db. 2.) Allow the user to specify their own password, but hash the password using some hashing algorithm - perhaps even your own custom written one - before sending that in the create user command to Postgres. Connecting via other means (pgAdminIII, M$ Access etc) would therefore fail as they cannot provide the same hashed password. 3.) Do as Korry suggested in appending (or perhaps prepending) a 'secret value' to the user supplied password. 4.) Combine all above options... Allow the user to specify their own password to which you append a hard-coded 'secret value' which has been encrypted in the source of your application and then you hash the resultant password string before sending to Postgres. My AUD$0.02 -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to allow users to log on only from my application not from pgadmin
Mark Walker wrote: I'm curious. How do you feel about having a scrambling algorithm embedded in your application, but having the scrambled password publicly readable in a config file? Does that seem secure? This is what you have to do if you want your users to connect to different databases choosing their own password. I never said anything about a readable config file. If your users are specifying their own password and you want to store passwords in a local config file on the users system then nothing is stopping you doing so. If this was the case I would put only what the user specifies in said config file, then when the password is retrieved from the file on application startup, perform your hashing/adding secret words etc. Anything I've done I do this way (in most cases though I use the registry rather then a config file since I deal primarily with weenblows. How would you deal with open source applications where the scrambling/unscrambling algorithms would presumably be public? Are there methodologies for developing custom algorithms that could be triggered during builds? Open source applications are a different situation altogether. The kind of security that Andrus appears to be looking for would give the impression that it is not an open source application he is dealing with. I could be wrong though. Having said that, yes if you are using an open source application any scrambling algorithms would be public, as would any passwords embedded within your source. In this case, then perhaps a 'proxy' application running on your own server would be the best option (I think someone else suggested this earlier in the thread) for serving the database requests. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to allow users to log on only from my application not from pgadmin
Mark Walker wrote: OK, I've thought about this a bit more and have come to the conclusion that storing the password locally in any way is completely insecure. Here are simple ways of hacking it: 1. If you use libpq in a shared lib(dll, etc). Replace PQconnectdb with your own version, rebuild and use your new dll to snatch the password. 2. If you use libpq in a static lib. Search for the PQconnectdb's image and do the same as #1. 3. If you don't use libpq. Search for strings that contain things like host = , user = , password = , etc and hack in your own code. I think there are really only 2 ways to securely deal with this: 1. Each user has a postgresql role in a way that I mentioned in a previous thread concerning the limit on number of users. You'd also have to secure your database via stored procedures and individual table role based access. This solution won't help the initial problem of users being able to connect with programs other then the original posters application. If the user has a role in Postgres and they know the username/password - which surely they will - then they will be able to connect using pgAdminIII, M$ Access, M$ Excel, any other program that can open an ODBC connection to look at and update a db which would then bypass any business rules that have been built into the main application. 2. Proxy server method. If it came down to it, a proxy server method would be preferable. Storing passwords locally would be anywhere from trivial to moderately difficult to hack. I accept your reasonings in not wanting the password held within the application. However that wouldn't rule out one of my suggestions in supplying Postgres a hashed password. I.e. a new user is set up and they specify their password as 'changeme'. If they know their password is 'changeme' they can open up pgAdminIII and log in with that password, or the same with access, excel et al. If however your application hashes that password and when doing the create role in PG, it sends the password through as xH6_33pq (made up hashing, not generated by any formula) using md5 to further encrypt and PG stores that, then the user can log in to your application which will do the translation in sending the md5 encoded ODBC connect request, however 'changeme' will not work in trying to connect from anything else, as that is not what PG has been given as the users password. Potential points of finding the password inappropriately will always exist. Regardless of what you do to secure your data, there's a fair chance that someone is going to get it. The question is, will the users of your application have the ability/tools to reverse engineer a program to find out its hashing algorithm or sniff network traffic and decrypt it to find the password being used in the connection string. As I keep telling my customers, the easiest way for me to secure your data is to unplug the network cable on the server. ;) -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 1: 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: [GENERAL] How to allow users to log on only from my application not from pgadmin
Andrus wrote: Run the application on a machine you control. Then the application can authenticate without the users being able to steal or piggyback on its credentials. Thank you for reply. My application is GUI applicatio which must run in customer computer and accesses to 5432 port in remote PostgreSQL server located in customer side over internet. I cannot control customer computers. Andrus. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match If the users have access to the database via having a username/password then it seems to me that they could use basically anything to connect via ODBC to the database and retrive/look at/update data. M$ Excel, Acces, reporting things like crystal reports etc and of course pgAdmin. If you hide the database username and password within your application (i.e. encrypted within the source code) so they cannot see the credentials that you connect to the database with internally then they have no means by which to connect to it using any other programs. What I gather is users in your case are set up as database users rather then having a users table on which your application authenticates. The downside of doing it the way you are doing it is always going to be that any user with a database username and password can connect to the database by any means they come by. I'm no Postgres expert, but I'm sure like any other RDBMS, postgres does not know, nor care, what application is doing the connection but rather just accepts an ODBC connection and the credentials that are passed to it. Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to allow users to log on only from my application not from pgadmin
Mark Walker wrote: One other thing. Another approach to this problem would be to have some sort of code signing/authentication capabilities for the postgresql server. For instance, you login as an administrator (some sort of enhanced privs), you get to look at the databases you have permission for. Otherwise, postgresql has to recognize the application. Has this ever been discussed? I don't think it would be feasible for any RDBMS to recognise the connecting application, certainly in my view the effort it would take to alter the postmaster/odbc driver and others would be a lot more than the apparent gain from having that functionality. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Any Plans for cross database queries on the same server?
Richard Troy wrote: [snip] My observation is that we have a real shortage of quality operating systems today, and what few exist/remain don't enjoy much market share because they're not based on Unix, so they're largely missing out on the Open Source activity. What may be worse, young people who don't know any better are sometimes told/taught not to bother with anything over five years old as it's antiquated so they don't ever find out that things could be better - and once were. (Example, anyone who thinks man pages are great has obviously got a very limited experience from which to base their opinion!) ... As a practical matter today we mostly have a choice of Windows or some flavor of unix, neither of which are great. That would be very different in my opinion if only Unix didn't have this asenine view that the choice between a memory management strategy that kills random processes and turning that off and accepting that your system hangs is a reasonable choice and that spending a measily % of performance in overhead to eliminate the problem is out of the question. Asenine, I tell you. Meanwhile, what Operating Systems ARE _today_ reliable choices upon which to run your Postgres datababse engine? [snip] Insert another plug for an OpenVMS port here Aside from the fact that HP's upper management don't appear to be aware of the existance of OpenVMS, it's a system that it hard to find fault with. On the alpha chip anyway... the Itanium is another story. It would be a very reliable choice on which to run a high-availability database As for your young people don't know any better comment... I'm a young 25 years of age, and I know much greener pastures than Weenblows or Unix. ;) -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Load balancing across disks
Im in the process of finalising my conversion from M$ SQL server to Postgres - all of which I'm very happy about so far. The database I work with has 37 tables, 5 of which run into the order of tens of millions of records and approximately another 10 can run into millions depending on the size of the customers main system. The DB is going to be expanded to have another 15 or so tables when I step up replication from my primary OpenVMS based application, some of these will also have extremely high usage - again depending on the size of the customers main system. In order to balance disk load and ensure faster data access, my current SQL server setup has the data spread across 3 physical disk devices. One question I would like to know which I can't find in the documentation I've been reading is if Postgres has any similar data distribution abilities. I.e. can I create a data file on D drive which holds tables a, b and e, and a data file on E drive which holds tables c, d and f. If this is possible, could someone point me to some documentation so I can experiment a little. If not possible, I guess I'll have to upgrade to some faster hardware... if they'll be willing to give me money for that ;) BTW, I'm using 8.2 on M$ Weenblows (Yes I know weenblows sucks, but i don't have sufficient unix/linux/other platform Postgres runs on experience to run the db on another server I'll wait for someone to port it to OpenVMS ;) and then use it on that (I know, I'm dreaming) - OpenVMS makes unixish systems look like they have the reliability of weenblows, I'll tell you that much :D) Cheers. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [Fwd: [PORTS] M$ SQL server DTS package equivalent
Thanks all for your tips and pointers. Looking at copy I think it may do just what I need. The tables I load the data into have the same columns in the same order as those in the CSV file. Loading data in this manner is going to be a rare occurance - just when we install a new customer site and need to do an initial transfer of data from the main system before we switch on my real-time replication program. The programs that extract these csv files already take care of duplicate key checking and so forth, so there shouldn't be any issues as far as data integrity checking goes. I.e. there's no actual data transformation, row merging and the like. Thanks again to everyone who's offered some advice, much appreciated. Regards, Paul. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installing Postegres side-by-side with M$ SQL server]]
Alban Hertroys wrote: Paul Lambert wrote: G'day folks, I'm faily new to the world of Postgre so excuse me if these questions seem ignorant. My current employer develops a software package which runs on OpenVMS on HP Alpha/Itanium servers and contains a custom database comprised of various format text and binary files. I.e. not in a real database engine. Having made a decision to port this SQL server database into Postgre I have a few questions before I get started: I was going to suggest running it on OpenVMS, but apparently it isn't supported? (http://www.postgresql.org/docs/8.2/static/supported-platforms.html) I only know the name and that it's supposedly rather reliable. Is it much different from your average UNIX? OpenVMS is an extremely reliable and secure operating system... it has a small few similarities to your Unix variants in terms of the interface, but only slight ones. In terms of the programming landscape on a system level, theres almost nothing common between Unix and OpenVMS. I would love a port of Postgres to OpenVMS - but I guess we all have unfulfilled dreams don't we :) I'd imagine there aren't too many VMS programmers around that would be willing to port Postgres either, but if anyone out there with experience in VMS wants to give it a go ;) I don't imagine it would be an easy task though - not something I'd look forward to doing anyway. Paul. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Installing Postegres side-by-side with M$ SQL server]]
Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/22/07 07:09, Paul Lambert wrote: Alban Hertroys wrote: Paul Lambert wrote: [snip] I'd imagine there aren't too many VMS programmers around that would be willing to port Postgres either, but if anyone out there with experience in VMS wants to give it a go ;) I don't imagine it would be an easy task though - not something I'd look forward to doing anyway. These are the categories of organizations running VMS: 1) Companies running a canned app for 15 years on an old, dusty late model (meaning mid-1990s) VAX or old Alpha that just keeps chugging along. Running a similarly ancient version of Rdb/VMS or Oracle or Ingres. Or very possibly runs atop the very rich RMS filesytem layer. (It's how you interact with files. Gives you simple access to sequential, FORTRAN, DAM ISAM files.) 2) Big companies running large SMP systems and relatively recent versions of Oracle Rdb or Oracle RDBMS, pumping millions of txn per day. 3) Hobbyists. Greybeards in love with VMS who have one or more Alphas (and maybe a VAX or two) in their basements, running apps and compilers with special non-commercial licenses. 4) A variant on #1. Running 5 year old hardware, and probably have a compiler license. Running Oracle Rdb or Oracle RDBMS. We are a #2 shop, and when we want a PostgreSQL instance, we don't run it on OpenVMS (since we need that horsepower for existing work), but we buy a box from HP and install Linux on it. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFtMZYS9HxQb37XmcRAoG4AJsFTyQB7hhoKDz4vM8k5AnKYfT+aQCdFV1F NW23JKKNDK7Za3pjw3I2fOU= =FQ/A -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match We've got pretty new hardware - DS15's, DS25's, Itaniums and so forth. But we don't run any DB app on it, most of our data sits in RMS files or flat binary files - even text files in a few instances, thus making standard DB queries nigh on impossible with the exception of a report generator we've built into the app, but that has nowhere near the capabilities of something like crystal reports or M$ Access, thus the need for a 'replica' standard db model. We looked at Mimer on VMS for a while but threw that out. We've got about 40 Alphas of various power levels and a couple of Itaniums on our WAN so plenty of grunt to spare though. About half of our programming staff would privately fit into category 3 too - myself included (though I did give away my vax a few years ago, leaving me with a solitary DS10) Having said that, I do agree with your point on those main 4 categories of VMS users and thus the unlikelyhood that anyone would be willing to do a Postgres port. Still a shame though :) Paul. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Installing Postegres side-by-side with M$ SQL server
Nicolas Barbier wrote: 2007/1/19, Paul Lambert [EMAIL PROTECTED]: A number of months ago I was pointed towards Postgre as a reliable database server Please don't use the word Postgre: url:http://stoned.homeunix.org/~itsme/postgre/. greetings, Nicolas My apologies, being relatively new to this engine I'm not well versed in the complete nomenclature. Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Installing Postegres side-by-side with M$ SQL server]]
Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/22/07 14:01, Paul Lambert wrote: Ron Johnson wrote: On 01/22/07 07:09, Paul Lambert wrote: Alban Hertroys wrote: Paul Lambert wrote: [snip] [snip] We've got pretty new hardware - DS15's, DS25's, Itaniums and so forth. But we don't run any DB app on it, most of our data sits in RMS files or flat binary files - even text files in a few instances, thus making standard DB queries nigh on impossible with the exception of a report generator we've built into the app, but that has nowhere near the capabilities of something like crystal reports or M$ Access, thus the need for a 'replica' standard db model. We looked at Mimer on VMS for a while but CONNX Easysoft appear to offer ODBC drivers for (probably only indexed) RMS files, if that helps you. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFtSFwS9HxQb37XmcRArreAJ0Y7hxAGemjhMnukvyHGLWDwWy1/QCdHx4t jJoVFkzeucdDivL9QEJXm5k= =uxvA -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: 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 Probably wouldn't - as I said we use a mix of binary, rms (some indexed, some not) and text files - so an odbc driver into RMS files would only give us a portion of the database. A lot of what I replicate to SQL server at the moment comes from flat binary files and a couple of the tables from text files, the rest from RMS files. Having this mix of types would mean we need to manage things ourselves, thus having our main in-house faux-database engine forward messages directly to the Weendoze box which then uses ODBC to pump the data into the database of my choice. It would make things a lot easier to manage if I could forward it to an ODBC database on the VMS machine - oracle is just way out of our reach though. Thanks for the suggestion though, that may come in handy down the track for another project I'm looking at. P. -- Paul Lambert Database Administrator AutoLedgers Level 3, 823 Wellington Street, West Perth, W.A. 6005 Postal: P.O. Box 106, West Perth, W.A. 6872 Ph: 08 9217 5086 Fax: 08 9217 5055 AutoLedgersTechnical Support Desk: 1800 649 987 (Free call) 08 9217 5050 (Perth local and mobile) Email: [EMAIL PROTECTED] http://www.reynolds.com.au For AutoLedgers technical support, please send an email to [EMAIL PROTECTED].
[GENERAL] [Fwd: [PORTS] M$ SQL server DTS package equivalent in Postgres]
Sorry, posted this to the wrong list :( Original Message Subject: [PORTS] M$ SQL server DTS package equivalent in Postgres Date: Tue, 23 Jan 2007 10:15:06 +0900 From: Paul Lambert [EMAIL PROTECTED] To: [EMAIL PROTECTED] G'day, Is there an equivalent in Postgres to the DTS Packages available in M$ SQL server. I use these in SQL server to pre-load data from CSV files prior to enabling replication from my primary application. Any pointers on where best to go for this would be appreciated. I'm reading about something called EMS, is that the way to go? Sample of one of the files I use: DEALER_ID^DATE_CHANGED^TIME_CHANGED^BILLING_CODE_ID^DES^BILLING_CODE_TYPE^LABOUR_RATE^LABOUR_SALES_GROUP^CUSTOMER_NO^PARTS_SALES_GRO f UP^COMEBACK^WORKSHOP^FRANCHISE^LOCATION^DELETEFLAG F65^23-Jan-2007^10:13^AA^ADVERSITING ADMIN^I^45^40^2196^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^AN^ADV NEW^I^45^40^1636^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^AP^ADV PARTS^I^45^40^1919^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^AS^ADV SERV^I^45^40^2057^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^AU^ADV USED^I^45^40^1775^18^^0^BLANK^0^N F65^23-Jan-2007^10:13^BA^B RM ADM^I^45^40^2823^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^BG^BUILDING MAINTENANCE GM HOLDEN^I^45^40^1311^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^BN^B RM NEW^I^45^40^2268^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^BP^B RM PART^I^45^40^2541^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^BS^B RM SERV^I^45^40^2680^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^BU^B RM USED^I^45^40^2401^18^^0^BLANK^0^ F65^23-Jan-2007^10:13^F^FLEET^C^50^27^0^17^^0^BLANK^0^ F65^23-Jan-2007^10:13^FC^FORD COMEBACK MECHANIC^I^65^21^140^19^Y^0^BLANK^0^ Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Paul Lambert Technical SupportTeam Leader and Database Administrator AutoLedgers Level 3, 823 Wellington Street, West Perth, W.A. 6005 Postal: P.O. Box 106, West Perth, W.A. 6872 Ph: 08 9217 5086 Fax: 08 9217 5055 AutoLedgersTechnical Support Desk: 1800 649 987 (Free call) 08 9217 5050 (Perth local and mobile) Email: [EMAIL PROTECTED] http://www.reynolds.com.au For AutoLedgers technical support, please send an email to [EMAIL PROTECTED].
[GENERAL] Installing Postegres side-by-side with M$ SQL server
G'day folks, I'm faily new to the world of Postgre so excuse me if these questions seem ignorant. My current employer develops a software package which runs on OpenVMS on HP Alpha/Itanium servers and contains a custom database comprised of various format text and binary files. I.e. not in a real database engine. In order for our customers to have a wider range of reporting on their database (via M$ Access, M$ Excel, CrystalReports, websites and other such facilities) I have some programs which replicate updates in this pseudo-database over the network to a Weendoze server on which I have a program written in VB which pipes the data updates via ODBC into an M$ SQL server. A number of months ago I was pointed towards Postgre as a reliable database server after which I signed up to a number of these mailing lists and have been reading them quite extensively since that time. Having made a decision to port this SQL server database into Postgre I have a few questions before I get started: 1.) Would running Postgre and SQL Server on the same machine cause any conflict with each other (other then competing for CPU/Memory) 2.) Would there be any drastic changes required to the VB program to get it to communicate or would the ODBC driver take care of everything for me? The program in question does nothing fancy, a simple take of the data coming across the network and pushing it into the designated database i.e. purley add/update single row at a time. 3.) Are there any significant differences in functionality between the two systems I should be aware of that may cause problems down the line. 4.) Can anyone offer any other pointers on what I should look out for when doing this transition. And given my lack of unix/linux/etc experience, yes it has to run on weenbloze - unless there is an OpenVMS port? ;) Thanks in advance for any suggestions/information. Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers
