een such thing during 8.1 installation.
Andrus.
---(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
dard changes to ddl statements and
thus is not good.
Best solution is as follows:
Planner must use index
CREATE TABLE foo ( bar CHAR(10) PRIMARY KEY );
for queries like
SELECT * FROM foo WHERE bar::CHAR(3)='ABC';
Can you make this patch or add this t
r CHAR(10) PRIMARY KEY );
and use SQL standard WHERE clause
WHERE bar LIKE 'ABC%'
or
WHERE bar::CHAR(3)='ABC'
I expect that primary key index can be used without non-standard extensions
to SQL language
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
3)='ABC' type expressions which do not
require ? and % escaping.
I understand that Postgres cannot use any index to speed up searches like
foo:CHAR(3)='ABC' which does not use regular expression match operators.
So LIKE with escaping is the only way.
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
A B "
"A C "
I don't see any space discrimination on sorting here.
I sorted the same data in Microsoft Word and got the same result.
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
dmin list. Though I'd recommend against
> calling the guru 'stupid' over there. :)
I'm creating application which upsizes existing data to new postgres 8.1
database.
Will I need to run VACUUM ANALYZE after upsizing ?
Must I enable statitics collec
sing PG 8.1 default postgres.conf file.
I read than autovacuum may skip some tables which can cause wrong guru
hints.
So it seems that I should run manually VACUUM ANALYZE for the whole database
once for initial statistics colection of those tables, isn't it?
Andrus.
---(
;foo' processed, 45 tables skipped, 12345 dead tuples
removed in 45 seconds
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
running analyze on the entire database
> would suffice.
Thank you. I now send ANALYZE command to Postgres after data loading is
completed.
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
to install itself as service running under SYSTEM or
under LOCAL SERVICE account?
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
t; under SYSTEM or under LOCAL SERVICE account?
>
> Can't do that with stock postgresql, you'll need to hack up your own
> version.
How to add some command-line switch to this, like
-installusingsystemaccount
Andrus.
---(end of broadcast)---
ostgres mydb
I'm bit new to Linux. I'm using white-box linux and Postgres 8.1.4
How to create backups of database with unique name in every night ?
Is there some script sample which can be called from /etc/crontab ?
Andrus.
---(end of broadcast)
which can create schema copy ?
How to make a copy of schema Company5 so that schema called Company6 is
created ?
Is there any sample code available ?
Andrus
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
ectory was empty.
How to install 8.2 from msi file with cluster initialization ?
Andrus.
Environment: Windows 2003 Enterprise server build 3790 (service pack 1)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
7104)
DEBUG: mapped win32 error code 161 to 2
FATAL: could not create shared memory segment: No such file or directory
DETAIL: Failed system call was shmget(key=1, size=1327104, 03600).
DEBUG: proc_exit(1)
DEBUG: shmem_exit(1)
DEBUG: exit(1)
child process was terminated by signal 1
initdb: removin
How to force server to accept only strong passwords:
At least 6 characters, must contain one digit, one upper and one lowercase
letter ?
Andrus.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
set datestyle to iso,iso;
select 1 where ('2006-10-31'::date, '-12-31'::date) OVERLAPS
('2006-10-16'::DATE, '2006-10-31':: DATE)
does not return any rows.
Why ?
How to make overlaps to return correct result?
Andrus.
I have a number of select statements (in 8.1 and 8.2beta) which assume that
overlaps returns true for those cases.
Which the best way to fix them ?
Should I use AND, OR and date comparison operators instead of OVERLAPS ?
Andrus.
---(end of broadcast
ueries I have column names and parameters instead of data
constants.
The only way it seems to replace OVERLAPS operator with AND, OR, <=
operators.
Is it so ?
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
. I use only dates as OVERLAPS arguments.
I changed all my WHERE clauses from
WHERE (a,b) OVERLAPS (c,d)
to
WHERE (a-1,b+1) OVERLAPS (c-1,d+1)
Will this give correct results ?
Andrus.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
uires writing a and b expressions twice. How to avoid repeating
expressions ?
Andrus.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
data provider implements cacheable interface which allows this.
Dataset is automatically refreshed using polling or async event notificaton.
I read the npgsql docs but havent found such feature.
Where to find sample which implements cached dataset in Postgres ?
Andrus
7;));
$_$ language sql;
It this best solution ?
How many times this is slower than expression in where clause?
Andrus.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
timestamps, but unfortunately not for
dates. Otherwise, I'd suggest that you use that instead.
I tried to use
timestamp 'infinity':: date
but this does not work if both b and d are infinity since
select timestamp 'infinity':: date<=timestamp 'infinity&#x
27;::timestamp, date '20060101'::timestamp) overlaps
(date '20060101'::timestamp, date '20070101'::timestamp)
returns false
So this cannot used for date overlapping.
Which sytax to use to substract/add a minute to make this correct?
Andrus.
t use maximum allowed date or max_timestamp casted to date.
Is it reasonable to use it ?
I hope that MAX_DATE <= MAX_DATE returns true.
Which is the value of MAX_DATE is Postgres ?
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
TE '-12-31' then this
check fails.
This is why I'm searching for a real MAX_DATE value in Postgres.
It would be nice if there will be MAX_DATE constant in Postgres or some one
row system table contains MAX_DATE value.
Andrus.
---(end of broadc
ns MAX_DATE value.
That is very interesting, but would you really expect to record dates
greater than the year ?
Some programmer who did'nt read the book you mentioned but some other sql
book may use
date '10001-1-1' for marking infinity.
So this will break by code.
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
select (date'20060101'::timestamp, coalesce(date'20060102'::timestamp,
'infinity')) overlaps
(date'20060102', coalesce(date'20060103'::timestamp, 'infinity'))
returns false but since date'20060102' is overlapping i
od start dates are the same, I need that in this case expression
returns true.
Is it possible to implement this using OVERLAPS operator ?
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
select * from information_schema.key_column_usage
returns
ERROR: more than one row returned by a subquery used as an expression
SQL state: 21000
How to reconstruct primay and foreign key statements ?
Andrus.
"PostgreSQL 8.1.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(
pg_dump source code) which
re-constructs statements to create primary and foreign keys ?
Andrus.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
eign key constaints and triggers and all
other places also ?
Documentation does not describe this.
Andrus.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ON pg_namespace.oid=pg_class.relnamespace
WHERE not pg_trigger.tgisconstraint
and pg_namespace.nspname='myschema'
Andrus.
---(end of broadcast)---
TIP 6: explain analyze is your friend
I noticed that that pgAdmin invokes pg_dump with -b command line parameter.
I havent found any documentation about this parameter in Psotgres Docs.
What is the purpose of this parameter ?
Andrus.
---(end of broadcast)---
TIP 4: Have you
to complete.
How to increace backup speed ?
Andrus.
---(end of broadcast)---
TIP 6: explain analyze is your friend
ance.
I don'nt know how to automate this 100% and havent found any such sample.
So I'm waiting when this is automated like 8.2 automates user privilege
dropping and use pg_dump in current installations.
Andrus.
---(end of broadcast)-
reases backup speed.
Info-zip zip.exe -9 config option does not decrease compiression speed
I expected that pg_dump uses same complression algorithm as zip.exe
I'm really wondering why -Z9 decreases backup speed significantly.
Andrus.
---(end of broadcast)---
r, which I had
> been rebuilding. I'm working to lower that downtime and will be doing
> another full-blown test in January or February.
I expect that full database backup created using pd_dump does not have never
have any problems on restore.
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
pen to public internet on server used by Postgres.
Your solution requires ssh port to be opened also but sysadmins refuce to
configure routers for second port.
How to use your solution in those cases ?
Andrus.
---(end of broadcast)---
TIP 6: expla
L and does not support Generic at bytecode level
Mono libraries are LGPL and it supports Generic at bytecode level.
6. Nowadays Microsoft controls Delphi and forces it to stop running in
Linux. Kylix has no upgrades.
Conclusion:
Only MONO/WinForms is a way to go in any serious applica
Or should I really write code which divides backup file to 1 MB chunks and
stores them in bytea field ?
Andrus.
---(end of broadcast)---
TIP 6: explain analyze is your friend
returns backup file with download speed ?
>> This problably requires implementing some file download protocol.
>
> Just don't try and do it within PG - use the tools the system provides.
How to force postmaster to run the tool which system provides when it
receives backup request instea
orts are closed at both sides.
I havent installed any updates. I start with 8.1 and installed last version
on new server. So some servers have 8.1.1,
some 8.1.2 etc up to 8.1.5
I havent touched old servers after system is set up.
Andrus.
---(end of broadcast)--
>> Not sure but using a binary cursor might improve things.
>
> Why not use COPY protocol?
I did full text search in Postgres 8.1 help file for "COPY protocol" but
havent found any matches.
Which is COPY protocol and how to use it ?
Andrus.
--
.
How this can be performed ?
Andrus
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
In my current DBMS I can use
create table t1 ( f1 int, f2 int );
create table t2 ( f3 int, f4 int );
update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4
This does not work in Postgres.
How to convert this statement to Postgres 8.1 ?
Andrus.
---(end of
procedural language
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
How to allow non-superusres to create database with language ?
Andrus.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe
After each error whole process must started again.
Andrus
---(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 thro
News reader cannot read
pgsql.interfaces.odbc
pgsql.interfaces.pgadmin.support
groups from news.postgresql.org news server
last message in odbc group is from 14.12.6 and in pgadmin group is from
18.12.6
pgsql.general and pgsql.announce newsgroups works OK.
Andrus
MS SQL server and Oracle does not have this issue and implement
ContinueUpdateOnError fast ?
Is this issue caused by PostgreSQL design failure ?
Andrus.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
so that it removes trailing spaces
itself ?
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
c2hartoffaccounts
(accountnumber) );
Is it OK to use duplicate foreign keys ? What issues will they cause ?
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
le to create a new table
public.commonaccountnumbers with accountnumber as primary key.
So the question seems to be:
can I use duplicate foreign keys without problems or must I waste resources
to create new table commonaccountnumbers ?
Andrus.
lap, having a separate table is probably better.
Using own table requires storing Postgres user name and password in client
computer. Thus this information is available to virtually everyone haveing
access to client computer.
So this is very bad idea and should avoided at all.
Andrus.
--
computer.
It is possible to obtain this information from client computer and use it
for unauthirized access to data.
Andrus.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan
My application implements field and row level security.
I have custom table of users where user privileges are described.
However user can login directly to database using pgAdmin. This bypasses
the security.
How to allow users to login only from my application ?
I think I must create server-side
rver side languages required to
re-implement appli server in PostgreSql server side.
I do'nt want to use PostgreSQL as application server by adding views , rules
and triggers since this all is huge work.
This is why I'm looking for application level authentication
stgres
always.
> Why not simply deny them access in pg_hba.conf?
I have 5432 port opened to public internet and users use my application from
internet.
pg_hba doesn't allow access per application basics.
Denying acces from pg_hba.conf also denies access from my applica
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
o disable customer running pgAdmin
Andrus.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
I installed 8.2 server on Windows XP
Today every instert to table causes error:
ERROR: attribute 13 has wrong type
DETAIL: Table has type character, but query expects character.
Any idea how to fix it ?
Andrus.
---(end of broadcast)---
TIP
last updates this record
I need also to add triggers so that updated and updatedby fields ae
automatically set when record is changed.
Where to find script which implements this ?
Andrus.
---(end of broadcast)---
TIP 6: explain analyze is
stored procedure ?
Andrus.
---(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
write
stored procedures in it.
Andrus.
Any idea how to write server-side stored procedures in C# for
PostgreSQL database ?
There's an old project called PL/Mono
<http://gborg.postgresql.org/project/plmono/projdisplay.php>, but as
far as I know it's unmaintained. You might want
C# than in
pl/pgSQL.
C# allows to use whole .NET class library, which contains solutions for
almost everything.
I think that using pgAdmin+PL/pgSQL decreases my productivity a lot compared
to Visual Studio + C#
Andrus.
---(end of broadcast)--
erved:
Error: Column name is duplicated
Expected:
t3 table should contain single id column
Andrus.
---(end of broadcast)---
TIP 6: explain analyze is your friend
My application receives the folllowing error sometimes.
Any idea how to fix ?
Andrus.
7/XX000:Error while executing the query;ERROR: tuple concurrently updated
CONTEXT: SQL statement "DROP TABLE templsabi"PL/pgSQL function "drop_table"
line 2 at execute statement
me time in same
database.
templsabi table is created, dropped and updated frequently by a number of
clients concurrently.
Do you have autovacuum enabled?
Yes.
Andrus.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet,
27;
I tried
CREATE OR REPLACE FUNCTION public.gomonth(date, integer,
out date) IMMUTABLE AS
$_$
SELECT $1 + $2'months';
$_$ language sql
but got error
ERROR: syntax error at or near "'months'"
How to implement this ?
Andrus.
---(
);
$_$ language sql
but got
ERROR: syntax error at or near "("
In VFP I can use
RETURN floor(INT((VAL(DTOS(ldDate))-VAL(DTOS(ldDob/1)
or
RETURN (year(ldDate) - year(ldDOB) - ;
iif( str(month(ldDate),2) + str(day(tdDate),2) < ;
str(month(tdDOB),2) + str(day(tdDOB),2), 1,
function gomonth(date, bigint ) does not exist
How to fix those errors ?
Andrus.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
I try to port application to PostgreSQL 8.1+
The following query runs OK in VFP but causes error in Postgres
FULL JOIN is only supported with merge-joinable join conditions
How to fix ?
Andrus.
SELECT
ametikoh.Nimetus as ametikoht,
Isik.nimi,
Isik.eesnimi,
koosseis.kogus,
COALESCE
_memory at a minimum.
I need to create installation for dumb users to ship DBMS with my
application.
So manual initial tuning is not possible.
How to force Postgres to use reliable settings by default?
How to use
shared_buffers=auto
work_memory =auto
in conf file ?
Andrus.
-
application runs ANALYZE command programmatically after data loading.
So end user does not need to run it.
Autovacuum is turned ON by default.
Why I should to run VACUUM ANALYZE in this case ?
ANALYZE must be sufficient.
Andrus.
---(end of broadcast)--
essing database "template1"
etc.
Should I fix something or can I continue to use this cluster normally ?
Andrus.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
table.
Is there any free toolkit or sample application for this ?
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
> Andrus wrote:
>> I want to create editable grid (client application) for large Postgres
>> table:
>>
>> At startup this grid show first screenful of records and allows to edit
>> them.
>> When user presses page down key, this grid should read next screenf
hich can be used kernel also.
> "GUI-Applikation in PHP"
To improve imagination you can download Agata Report from
http://www.agata.org.br/us/index.php
This is is GUI application written in PHP which allows to create reports
from Postgres database.
Andrus.
---
tributing using news protocol ?
Andrus.
---(end of broadcast)---
TIP 6: explain analyze is your friend
builder.
I looked their web site. They have the following suggestion in
http://phplens.com/lens/product/ :
... , you will need to ALTER TABLE phplens MODIFY id CHAR(40).
MODIFY clause causes error in Postgres. Does this mean that they do'nt
bother about Postgres compatibility at all ?
Andrus
y simpler solution for offline reading ?
Andrus.
---(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
it not possible to set archive_command parameter to
unset state from Postgres client.
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
"Is anyone else having problems with 9? "
I use VFP 9 + Postgres 8.1 on XP + 105 version of Postgres Unicode ODBC
driver without problems.
Andrus.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your
Thisis fixed this in December 2005.
Recent development snapshot does not have this problem.
This problem occurs only when Postgres returns error and VFP application
executes next statement i.e. in rare cases.
Andrus.
>There is an ODBC problem with Visual Foxpro 9. According to a Vis
greater than or equal to 10^9 for field with
precision 9, scale 3.;
DETAIL: The absolute value is greater than or equal to 10^7 for field with
precision 9, scale 3.;
Why Postgres 8.1.1 in XP returns different exponents ( 10^6, 10^9, 10^7) for
field with precision 9, scale 3 ?
Andrus
sibilities:
1. Create a loop over all database objects and issue change owner statements
2. Update system tables.
3. Make database dump and restore
4. make text dump and run some global find/replace. I'm not sure which
utility can handle it in windows
Is there any sample how to do it in Wi
t execute GRANT ALL ON SCHEMA commands for each schema manually,
right ?
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
ction.
However, both tables are created.
Any idea hot to force parent transaction rollback to roll back committed
nested transactions ?
Andrus.
---(end of broadcast)---
TIP 6: explain analyze is your friend
ase
if one of its commands (APPEND FROM TABLE) fails
I have'nt found a way to disable this ROLLBACK
So I'm looking a way to force Postgres to ignore this ROLLBACK
Andrus.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
How to install Postgres 8.1 on PowerMac 10.1, OS X 10.4.4 ?
Is there ready made binaries available for download of should I build from
source ?
Where are installing or building instructions for Mac available ?
Andrus
---(end of broadcast
applications ?
Using Postgres 8.1
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
with any postgres.conf and other postgres config
settings and with any user access rights ?
Andrus.
---(end of broadcast)---
TIP 6: explain analyze is your friend
n Linux
interpets this setting ?
Andrus.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
> It is not because of the locks. There is only running transaction (and it
> got every locks). Not sure why the backend stucks there, seems it doesn't
> aware of the broken client. In normal situations, if you killed a client,
> then the server will print something like "could not receive data fro
cess does not respond to cancel signal from main
process.
I can reproduce this only in production database. I in test database it
works OK.
Andrus.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
converted to
a 2 MB hex strings before uploading through odbc.
File is uploaded, stored and downloaded using localfil table in a number of
records each containing 1 MB of data.
Is it more reasonable to use smaller blocks to load and store files ?
Andrus.
My table strucure is
CREATE TABLE public
network bug
> that caused the backend not to be told that the connection was lost.
If so why pgAdmin Cancel command does not terminate the process ? If process
is waiting for data,
Cancel signal sent through pgAdmin should terminate process immediately.
Andrus.
--
I find that my killed connection server process process disappear in Status
after a long time.
> If pgAdmin's cancel is just dropping the connection, the server might
> take some time to notice it, especially if it's in the process of running
> a query and doesn't have reason to talk to pgAdmi
tor does not remove trailing spaces.
How to concat strings in this expression so that trailing spaces are
significiant or how to re-write this join condition so that trailing spaces
are used for comparison?
Andrus.
---(end of broadcast)---
TI
401 - 500 of 586 matches
Mail list logo
