Sushant,
Can this fit?
>
> select plainto_tsquery('english', 'the quick brown fox jumped over the
> lazy fox');
> plainto_tsquery
> -
> 'quick' & 'brown' & 'fox' & 'jump' & 'lazi' & 'fox'
>
no, this cannot fit. This just adds
I want to access the single words in a text. Better yet: the relevant words
(i.e. without stop words) in a text.
to_tsvector or casting gets me the lexems as a tsvector:
select to_tsvector('the quick brown fox jumped over the lazy fox')
''brown':3 'fox':4,9 'jump':5 'lazi':8 'quick':2'
And I wou
>
> hashtext
>
I believe it's considered an internal function, and not one to rely
> on. If you search the archives, you will find reports around a version
> upgrade when it changed the output for a certain input and thus broke
>
yes. I did find that discussion, and a blog post by Peter E. abou
hello,
just tried to lookup the documentation of hashtext.
And I did not find it by:
a) searching the online docs with the integrated searchengine
b) reading through the chapter 9 "functions and operators"
-> within string functions, there is md5(string), but not hashtext.
c) searching post
>Do I save disk space by having them as NULL instead of FALSE? So my
>>application would have conditional code for NULL and TRUE, instead of >FALSE
>and TRUE.
The short answer:
do not even think about it.
NULL has a well defined meaning within SQL: "we do not know the
value", with "well defin
yes, there is.
select from where
like '%'||chr(x'42'::int)||'%'
where '42' is your hexadecimal character value.
Be sure to read and understand everything you can find about encodings; and
make sure the hexadecimal value you are searching for is from the same
encoding.
Best wishes,
Harald
Hello Dario,
When an index is available for a query, the planner decides whether to use
> it or not depending on whether it would make the query perform better,
> right? However if an index, which does not exist, would make the query run
> better the planner is not able (allowed?) to create such i
>> My question: Which way is available to query the linked libpq version?
> But it does outline that fact that it wouldn't suck to have a >function in
> libpq returning the version so that application can >check this at runtime -
> clearly it would also be useful when >being linked "through" s
Brady,
Then I attempted to mount a normal encrypted volume with TrueCrypt, move the
> data\ and sub-folders to this volume and reconfigure PG to point to this as
> the data folder. Now, the PG service will not start at all.
>
> moving data and subfolder on NTFS is a Level-20 operation. The usual
as we get more and more questions from users of their software, maybe it
would be helpfull for both sides if they decide to get more involved within
the PostgreSQL community.
Harald
--
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
Amtsgericht Stuttgart, HRB 7
Bill,
>
>
> We got this same kind of thing working by using PostgreSQL env variables.
> First, set custom_variable_classes in your postgresql.conf. You can then
> use the SET command to set variables of that class, and use them in your
> functions:
>
> that is an interesting hack. Just googled up
Gabriel,
what you are looking for is also called "session variables". There are
essentially 2 kind of receipes in the wild:
a) store those session information in temporary tables
b) store those session information in shared memory
version a) has the advantage that it can be done via plpgsql, and
For the record:
> Should'nt plplython.dll be a valid win32_x64 application when being loaded
> > into PostgreSQL 64bit? Could this be the source of the error? (as in:
> > "plpython.dll is 32 bit, PostgreSQL is 64bit, so no loading of
> 32-bit-module
> > into 64-bit-process?)
>
> plpython.dll is un
Hello,
I am using PostgreSQL 9.0.1 on Windows 7.0 Ultimate 64bit.
I tried to use plpython within one of my databases, but installing the
language fails with:
C:\Program Files\PostgreSQL\9.0\lib>createlang --dbname=ibox --echo -U
postgres plpythonu
SELECT oid FROM pg_catalog.pg_language WHERE lan
Dmitriy,
>
> Again, I suggest PQlibVersion() or PQversion() instead of PQlibpqVersion().
>
> Why this "pq" redundancy in name? Waste of bytes.
>
> the first PQ qualifies the name-space "those are PostgresQl functions".
the library is called libpq, and we are explicitly asking for the version of
li
>
> And the dynamic linker will take any version of libpq.so that has the same
> major number, meaning that you
> cannot assume that it's not 8.4 just because you built against 9.0.
>
> [...]
> Not to mention when you're on windows (and I dunno if any other
> platforms are like *that*), where the
Peter,
> This is normal. Postgres is said to have a multi-process architecture
> (which is often contrasted with a multi threaded architecture). The
> number of processes isn't very predictable.
>
> you are right, it is normal.
But the number of processes is quite predictable:
on default Postgr
>
>
> > We could add a PQlibpqVersion(), maybe, but it would be many years
> > before client code could rely on that being present.
>
> I think we should.
>
> And in a small way they can already - if they check for it
> dynamically, they'll know if it was 9.1 or newer at least :-) It'll be
> a long
>
> I would imagine that most libpq wrapping drivers use libpq's
> PQserverVersion(), which returns an integer that looks like 9.
>
> exactly that is exposed as cn.server_version property of the psycopg2
connection object; but it does not help, as a 8.4libpq happily connects to a
9.0 server...
With 9.0 there is the new hex encoding for BYTEA, which is activated by
default.
libpq BEFORE 9.0 are not able to decode that encoding.
I am programming with Python, using psycopg2. When psycopg2 is linked with
libpq 9.0, everything works fine with hex-encoding; if psycopg2 is linked
with libpq <
Arjen,
> You do need to be wary of malicious users who put a first name of a
>few hundred megabytes.
yes, but if that "my first name is a video" hits the database, it is
allready to late, isn't it?
If it is open to the public, input should be sanitized WAY earlier;
and for an internal applicatio
I recommend to use TEXT as type for that kind of columns.
99 out of 100 theories about "this value will never be longer then xx
characters" fail in the long run.
And "text", limited only by PostgreSQLs limits, performs as good or
better then varchar(length_limit) The time of "we only can allow n
c
Howard,
that was a great read!
I especially like your sentence
""" Considering that any search containing more than a half-dozen
search terms is more like an essay than a realistic search; and
considering that returning half a million matches is more a data dump
than a sensible search facility,"
Rohit,
yes, there is.
- Click on the Start-Icon (XP) or Windows-Icon(W7) to bring up your
ProgrammsMenu
- click on "Control Panel"
- click on administration icon
- DOUBLE-CLICK on planned tasks
- click on new planned tasks, in the add planned task wizzard you can add
the commands for running pg_
John,
> It's the user created by the one-click installer. I believe it owns the
> postgres data directory and is used to start the server. Other than that,
> the intention is for this user to have no other file privileges. The default
> is "postgres" but it could be anything.
>
> doing the defaul
John,
I have noticed that postgres is very unhappy if the proper "postgres" user
> doesn't have access to the files. But I have also noticed that other users
> seem to be able to have access without causing problems.
can you please give more information about the (windows)-user "postgres" ?
is
Hello,
within Section 34.9.6. of the PostgreSQL documentation (
http://www.postgresql.org/docs/8.4/static/xfunc-c.html) there is an
excellent summary how to compile and link extensions on a variaty of Unix
and Unix-like operating systems.
How do I do the same on Windows, using Visual C Express ?
>Hi. What does "PGDG" mean, as in PGDG84, the PostgreSQL >repository
containing PostgreSQL 8.4.x, etc?
Postgresql Global Development Group ?
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
Us
Dann,
There really are domains that big, so that there is no more normalization or
> other processes to mitigate the problem.
>
> Examples:
> Microsoft's registered customers database (all MS products bought by any
> customer, including operating systems)
> Tolls taken on the New Jersey road syste
Ritas,
>modules. Please check the installation logs in C:/docs&setttings/user/local
>settings/temp for details". The log file doesn't have any error, but it
that directory name for "docs and settings" looks strange to me.
(disclaimer: using German language versions of Windows); I am used to
"docu
>
> No, I'm not storing RDBMS in S3. I didn't write that in my post.
> S3 is used as CDN, only for downloading files.
>
So you are storing your files on S3 ?
Why should you store those files additionally in a PostgreSQL database?
If you want to keep track of them / remember metadata, hashes will
Ognjen,
> Is there a way to run Postgres in embedded mode? More precisely, to run it
>>> without using TCP/IP port, and without installing as a service?
>>> I am aware of Unix-domain sockets, but is there something similar for
>>> Windows?
>>>
>>
>> on unix, as you imply, it can be run with unix
Dave,
> >
> > IIRC, that was modeled on where Microsofts own SQL Server put it's
> > data files by default. Does anybody know if that has changed recently?
>
> It hasn't. I checked 2008 this morning.
>
>
And how does SQL Server fiddle that with Windows 7? My experience is that W7
is "not amused" w
I want to provide a "suggest word as you type" feature in an application
(like google suggest).
All the documents are - of course - stored within a PostgreSQL database,
within TEXT columns.
To provide those suggestions, I need a list of all words, together with a
number indicating the number of oc
Eddie,
> It also seems to show a weakness in PostgreSQL's logging, in that (a) it
> would help a lot if it just printed its error to stderr, and (b) the log
> message it did send to the event log was of the form "directory not found"
> rather than "permission denied".
>
problem for sending to st
> As I mentioned on the list a couple of months ago we are in the middle
> of stuffing a bunch of molecular data (including entire genomes) into
> Postgres. If anyone else is doing this I would welcome the
> opportunity to discuss the issues off list...
>
>
I do not stuff molecules or genomes or
Dave,
please also check out the licence and costs terms in detail.
Especially: is it given that the planned usage willl continue to be
within the allowed bounds for MySQL-GPL? Are otherwise the costs for
MySQL-commercial budgeted or a reserve founded?
PostgreSQL has here a GIANT advantage with a
Greg,
>8MB working out best is
> really unexpected though; I'd like to know what you were doing where *that*
> was the optimal setting.
Developer machine, XP professional, 1 Gig memory. other applications
are running, ~2-4 concurrent acceses to PostgreSQL. Value was in the
200MB range, system tra
>> I got MUCH better results by drastically lowering shared_buffers on
>> Windows. Drastically = 8MB.
>
> Wow - really? Greg and I did some rough pgbench experiments last year
> and were finding the on a 4GB machine, running XP Pro, 512MB seemed to
> be optimal, but it was only marginally better th
> # - Memory -
> shared_buffers = 512MB
> work_mem = 256MB
this settings would be good for PostgreSQL on Unix and Unixlike systems.
I got MUCH better results by drastically lowering shared_buffers on
Windows. Drastically = 8MB.
please try it. (there is no clear understanding WHY shared buffers on
Dave,
> The closest thing we have to a client installer is probably pgAdmin,
> which will install libpq and it's dependencies, as well as psql,
> pg_dump/pg_dumpall/pg_restore, and of course, pgAdmin.
maybe we should promote this information to the public?
Calling "pgAdmin" additionally "the cli
Hello Jonathan,
I had some challenges with the 8.4.1 windows one click installer,
especially as in some environments I am not "allowed" to use a local
postgres account. So to install 8.4.1 on various windows machines I
use the following process:
a) download the "binaries only" distribuion
b) crea
Hello,
in 2003 there was a thread started by Hans about dblink_ora...
http://archives.postgresql.org/pgsql-hackers/2003-06/msg00361.php
it "ends" with something about "propably not ready for 7.4".
There was the proposal to integrate it within the normal dblink.
Did happen something, and what,
Thom,
> I'm wondering if anyone has experience of storing and getting images to and
> from a database?
Yes. For a customer I have one application running for ~8 years which
stores images (and various other BLOBS) within a PostgreSQL database.
Started with 7.something, now running on 8.3; allways
Thom, depesz, silly,
> SELECT DISTINCT ON (id_bf) id_bf, wert, letztespeicherung
> FROM rfmitzeit
> ORDER BY id_bf, letztespeicherung ASC;
yes, that does work. I put it in the real world query (my example was
reduced to the relevant parts), and it provides an impressive speedup
(down from 2234 to
> > Is it possible? How would the SQL utilizing WINDOW-functions look like?
>
> there is no point in using window functions in here - simply use
> "DISTINCT ON".
and how would I use DISTINCT ON for this query? Please bear in mind,
that there is more then one id_bf (just stopped the sample data wit
id_bf, wert, letztespeicherung:
> 98, 'blue', 2009-11-09
>> 98, 'red', 2009-11-10
>> now I have a select to get the "youngest value" for every id_bf:
>>
>
> Not tested:
>
> SELECT id_bf, wert,
> max(letztespeicherung) over (partition by id_bf)
> FROM rfmitzeit
>
no, that
I have a table
CREATE TABLE rfmitzeit
(
id_rf inet NOT NULL,
id_bf integer,
wert text,
letztespeicherung timestamp without time zone
CONSTRAINT repofeld_id_rf PRIMARY KEY (id_rf),
);
where for one id_bf there are stored mutliple values ("wert") at multiple
dates:
id_bf, wert, letztesp
Ambarsih,
> // add.cpp : Defines the entry point for the DLL application.
are you sure that you using the C-Compiler and not the c++ compiler?
As much as I know about defaults, that will be a C++ compiled
function, which is something totally different then a C compiled
function. (and, to my knowl
Bob,
>
>
> While looking at an error message for iexplore.exe, I noticed, on Task
> Manager, that there are 5 or more instances of postgres.exe running. Each
> instance is consuming between 7 to 10 megs of resources, for a total of
> almost 50 megs.
>
> Is this normal behavour?
>
> 5 instances is t
Hello Bill,
some words to the installation of PostgreSQL on Windows:
There are 3 user accounts involved:
a) the Administrator account (or user with Administration privilege)
- This user is running the installer. It should have Administrator
privileges, because only Admins are allowed to register
Adrian,
While I was walking the dog I thought of a better solution.
>
> sql_str = """ALTER TABLE %(xn)s OWNER TO xdev;
> GRANT ALL ON TABLE %(xn)s TO xdev;
> REVOKE ALL ON TABLE %(xn)s FROM PUBLIC;
> GRANT SELECT ON TABLE %(xn)s TO PUBLIC;"""
>
> cur.execute(sql_str,{'xn':table_name})
> --
>
Thi
I know that is possible via
EXPLAIN and EXPLAIN ANALYZE
to get planned and actual time for one query - but no results.
I am looking for information like:
"to do this query, I had to":
- read x from disk/os.cache
- get n from my own buffers
- do n1 comparisons of strings
- do n2 comparisons of
Loic,
>settings up each time. The added benefit of doing a per schema dump is
>that I provide it to the users directly, that way they have a full
>export of their data.
you should try the timing with
pg_dump --format=c completedatabase.dmp
and then generating the separte schemas in an extra st
>nope...it's an array of composite types, each type with one field, and
>array of two ints. this is waht you wanted? what are you trying to do
>exactly?
yeah, that is quite what I want, just was "surprised" by the way psql
displayed the result. What I am doing:
I have a table:
key1 key2 key3 in
Sam,
> To all: is there a deeper reason why there is no array type for datatype
> record available?
> Not enough demand :)
>
seams reasonable :)
> Try:
>
> CREATE TYPE intarr AS (arr int[]);
> SELECT array(
>SELECT x::intarr FROM (
> SELECT array[2,3]
> UNION ALL
> SELECT
Sam,
No, as depesz says it's not doing that. Depending on what you want out
> you can get most of the way by having an array of ROWs that contain an
> array of integers. You just need to change:
>
> the sad thing is:
select array(
> select x from (
> select array[2,3] as a
> union
> selec
Hello,
if I use this statement:
postgres=# select array[[2,3],[3,4]];
array
---
{{2,3},{3,4}}
-> the result looks for me as an array of integer-arrays
now I try:
select array(
select a from
(
select array[2,3] as a
union
select array[3,4] as a
) x);
and the result is:
FEHL
trying to run
postgresql-8.4.0-1-windows.exe
as a user without admin-privs leads to:
"PostgreSQL has detected a problem and has to be ended" (on german)
Problem-Signator:
AppName: postgresql-8.4.0-1 -windows.exe
ModVer: 1.0.0.0
Offset: 0004df8b
the same also happens when only trying to read th
Hello,
I have an old PostgreSQL Database which was created with encoding =
SQL_ASCII. That is an old sin of mine; 9years ago I did not know better.
Now I know better than to use SQL_ASCII.
On the bright side: I am totally sure that the TEXT within that database is
in WIN1252 / CP1252 encoding.
a quite interesting read.
http://www.codingthewheel.com/archives/stranger-than-fiction-story-online-poker-tracker-postgresql
especially as an explanation of the growing number of questions from
Windows-Users of PostgreSQL
And ... for a tag line: "PostgreSQL. Thousends bet on it."
Harald
--
GH
Luca, in addition to this:
that feature is on the "Features We Do *Not* Want " list of the
http://wiki.postgresql.org/wiki/Todo list:
Obfuscated function source code (not wanted) Obfuscating function source
code has minimal protective benefits because anyone with super-user access
can find a way
CMJ,
same system.Even under moderate load in my application, each of the postgres
> process occupies some 25 MB memory leading to total postgres memory usage of
> 500+ MB. Now, how do i limit the memory used by each
how did you measure those 25MB and those 500+MB MEMORY usage?
I guess you are
Probably you do not have any script to start PostgreSQL, but it is started
as a service.
The data directory is a parameter of the service within services.msc, there
is
... pg_ctl.exe ... -D "":\your\directory"
Read up on pg_ctl --help how to change this information
(pg_ctl --register)
Best wish
Bruce,
I am thinking your best solution is to create a table with a uuid column
> and reference that to sync up your data. That would also allow data
> dumps to be restored to another machine with the proper identifier
> because the identifier is really a characteristic of the data, not of
> the
Bruce,
> would be "database system identification" - the rather unique ID of a
> > database installation. Third line in pg_controldata output:
> >
> --
> > Datenbanksystemidentifikation:5293702
nbank-Cluster-Status: im Produktionsmodus
pg_control zuletzt geändert: 02.06.2009 10:54:27
Best wishes,
Harald
On Tue, Jun 2, 2009 at 4:19 PM, Bruce Momjian wrote:
> Massa, Harald Armin wrote:
> > Hello,
> >
> > is there any way to ace
Gilles,
If some users have been using the native Win32 version of PG in production,
> I'd like to hear how it runs in terms of stability and performance. This
> DBMS is meant to be used in SOHO settings, meaning it should be very easy to
> install, use, and kept up-to-date.
>
I have used the nati
Raphael,
not shrinkwrapped, but have a look at
http://www.trirand.com/blog/
jquery Grid Plugin. With some backend programming (Python, PHP...) you will
have a table quick; forms are created "automagic" from that table;
formatters etc. are definable.
Are there any tool/libs to easily and rapidly
Hello,
I had some spurious effects of the PostgreSQL-Service not starting on
windows. The usual fix was to "reboot", or even simpler: net start pgsql-8.3
TODAY it happened on my machine, so I had more access to track down some
logs. And so I want to share some of them, the course of events:
Wi
Hello,
is there any way to acess the pg_controldata information via SQL?
(running pg_controldata via shell needs file access to the postgresql data
dictionary, which is usually not given)
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stutt
> What did make me scratch my head was a short stream of @ symbols (well they
> show up as @ symbols in vi) in the log file of the main server (others are
> slony subscribers).
>
> mentioning those @ symbols ...
1,5 weeks ago there was reported on this list the problem "postgres service
not st
Bill, Jennifer,
> *shared_buffers = 1024 # min 128kB or max_connections*16kB ## Also to
> low.
> > Right? I've got 3GB to work with!*
>
> Assuming that's equating to 1G, then the value is about right. Common
> best practice is to set this value to 1/4 - 1/3 of the memory available
> for PostgreS
Jennifer,
1.) you should state the exact version of your operating system
>
> **
> *Windows Web Server 2008 x64*
> **
>
>>
2.) you should state the exact PostgreSQL Version
>
> **
> *The latest available, installed this : postgresql-8.3.7-1-windows.exe*
>
>
>> 3.) exactly describe your Windows Aut
Jennifer,
obviously you are on windows. And the problem you are describing seems to
be:
"after running the tuning wizzard, the PostgreSQL Service does not start any
more"
AT the same time you are describing some problem of
"you are not able to delete some Files / Directories connected with
Post
75 matches
Mail list logo
