PostgreSQL

21 September 2024 20:36

Notabug, generally.

(Well, maybe pg_dump would have to deal with that sometimes).

Set search_path in your function to include public. pg_dump explicitly resets it to nothing.

PostgreSQL

21 September 2024 20:21

Getting error no match for argument: libmemcached

PostgreSQL

21 September 2024 20:20

install the dependenies needed for your pgpool version

PostgreSQL

21 September 2024 20:16

While installing pgpool getting error

PostgreSQL

21 September 2024 19:03

Hm. Am i hitting a bug?

While restoring my database schema, i have CREATE EXTENSION ltree in both my schema and public, however it fails when a SQL function with ltree in it is defined.

PostgreSQL

20 September 2024 23:27

Then it prefers a scan which baffles me

PostgreSQL

20 September 2024 23:26

Timescale and uri grouping?

PostgreSQL

20 September 2024 22:34

Doh! Seems that postgresql still prefer btree into equal comparison. Temporary remove the btree index (is it a composite or partial index?) and see what happens could a viable way without impacting your system?

PostgreSQL

20 September 2024 21:05

The fact is that no matter what I do postgresql seems to straight skip the hash indexes

PostgreSQL

20 September 2024 17:58

I've not a 2TB url table to test but googling I've found this article. I'm not sure can fit your data but generally urls are long.
https://evgeniydemin.medium.com/postgresql-indexes-hash-vs-b-tree-84b4f6aa6d61

PostgreSQL

20 September 2024 15:48

I have a table full of webpage snapshots, with the following columns:
- crawl_id = unique id of the webpage snapshot
- uri = url of the webpage (not unique)
- taken_on = timestamp of the snapshot

99% of the lookups are done by exact matching of uri. The table is quite big (2TB) and i would like to partition it. Is hash really the only way here with a sensible number of partitions considering my memory?

PostgreSQL

20 September 2024 04:51

Hello and welcome to this chat. Here we discuss PostgreSQL and provide best-effort voluntary support on Postgresql issues.
If you have any question, try asking kindly, including log files, error descriptions and perhaps what you're trying to do.

- 🚫 photos and screenshots
- 🚫 spam and offtopic
- ✅ questions with log files and clear explanation of what's gone wrong

⬇️ Press the button down here ⬇️

PostgreSQL

19 September 2024 15:04

Im quering it inside server via local domain socket

PostgreSQL

19 September 2024 14:08

Without looking at the actual data? 🤷

PostgreSQL

19 September 2024 13:28

So do u have any alternative to eradicate it.

PostgreSQL

21 September 2024 20:22

search for it then and find a github repository for pgpool specifically, u will find a lot talking about different issues surely u will find a match for your reuqest if it is a common issue or just pull your logs on it and wait for answers

PostgreSQL

21 September 2024 20:20

Try to install libmemcached

PostgreSQL

21 September 2024 20:18

-nothing provides libmemcached.so.11()(64bit) needed by pgpool-II-pg16-4.5betal-1pgdg.rge19.x86_64 from pgpool145

PostgreSQL

21 September 2024 19:04

CREATE EXTENSION IF NOT EXISTS ltree WITH SCHEMA public;

CREATE FUNCTION public.escape_ltree(text) RETURNS public.ltree
    LANGUAGE sql IMMUTABLE STRICT
    AS $_$

WITH string AS (
        SELECT array_to_string(array_agg(
                        CASE
                                WHEN chr = '' THEN ''
                                WHEN chr = '_' THEN '__'
                                WHEN chr ~ '[0-9A-Za-z]' THEN chr
                                WHEN strpos('-! "#$%&''()*+,./:;<=>?@[\]^ ^`^y`{|}~\    ', chr) != 0 THEN '_'||substr('0123456789abcdefghijklmnopqrstuvwxyz', strpos('-! "#$%&''()*+,./:;<=>?@[\]^ ^`^y`{|}~\    ', chr), 1)||'_'
                                ELSE '_'||to_hex(ascii(chr))||'_'
                        END), '') AS ee FROM (SELECT regexp_split_to_table($1, '') AS chr) a
        ) SELECT (CASE WHEN length(ee) >= 256 THEN ('_H_'||encode(sha224(convert_to($1, 'UTF8')), 'hex')) ELSE ee END)::ltree FROM string;
$_$;

psql:foxo_blue_schema.sql:718: ERROR:  type "ltree" does not exist                                                  
LINE 12: ...24(convert_to($1, 'UTF8')), 'hex')) ELSE ee END)::ltree FROM...

PostgreSQL

20 September 2024 23:34

I mean it's storage could pack the data by uri grouping rather effectively.

Also, some partition improvements in the timescale code may be good for you.

PostgreSQL

20 September 2024 23:27

But i don't care when the page was crawled actually

PostgreSQL

20 September 2024 22:36

Is there an order by 'url' in your query?

PostgreSQL

20 September 2024 21:05

Btree are always used

PostgreSQL

20 September 2024 17:59

Ideally, with a great performance improvement with huge data, hash index seems good (seems)

PostgreSQL

20 September 2024 17:50

Just curious: index on uri is a btree, hash or other type? If not hash, can a hash index increase performance without using partitioning method?
Just curious 😅

PostgreSQL

20 September 2024 13:18

yes thats client side error, but serves has plenty of memory

PostgreSQL

19 September 2024 15:51

You're aware that that's psql running into issues, right? shared_buffers don't matter...

PostgreSQL

19 September 2024 15:02

hi everyone, I have 5gb matherialized view, when quring via psql I get out of memory for query result, shared buffer 16gb

PostgreSQL

19 September 2024 13:47

Each function as a separate SQL file in Git, a bash script to build the schema update file.

PostgreSQL

19 September 2024 12:57

There's obviously some garbage in your source data, or you misconfigured either the export or import config. My guess is that at some place, a NULL gets converted to a binary 0 and that's in turn sent as a text.