2018-10-22         Woodrow

Spring data query issue in ManytoOne maping

I am using Spring Data, along with postgresql. And I have ManyToOne mapping in City named entity calss. When i execute a select query i got following error.@Entity@Table(name="\"City\"")public class City implements Serializable { private static final long serialVersionUID = 1L; @Id @Column(name="city_id") @GeneratedValue(strategy=GenerationType.IDENTITY) private Integer cityId; @Column(name="city_name") private String cityName; @ManyToOne(fetch= FetchType.LAZY) @JoinColumn(name="state_id") private State state; @Column(name="district_name") ...

 postgresql                     1 answers                     61 view
 2018-10-22         Betty

PostgreSQL - Converting Binary data to Varchar

We are working towards migration of databases from MSSQL to PostgreSQL database. During this process we came across a situation where a table contains password field which is of NVARCHAR type and this field value got converted from VARBINARY type and stored as NVARCHAR type. For example: if I execute SELECT HASHBYTES('SHA1','Password')` then it returns 0x8BE3C943B1609FFFBFC51AAD666D0A04ADF83C9D and in turn if this value is converted into NVARCHAR then it is returning a text in the format "䏉悱゚얿괚浦Њ鴼"As we know that PostgreSQL doesn't support VARBINARY so we have used BYTEA ...

 postgresql                     2 answers                     62 view
 2018-10-22         Blake

An operation is already in progress error while accessing DB from middleware

Hello,I have an ASP.NET Core app with EF Core running on postgresql db server. I ran into this problem:When I access DB from a controller, everything works fine.But when I try to access DB from a middleware, an exception occurs (both under higher request load):System.InvalidOperationException: An operation is already in progress. at Npgsql.NpgsqlConnector.StartUserAction(ConnectorState newState) at Npgsql.NpgsqlCommand.<ExecuteDbDataReaderInternalAsync>d__4.MoveNext()...Sample code:DB configurationprivate void ConfigureDatabase(IServiceCollection services){ var...

 postgresql                     2 answers                     44 view
 2018-10-22         Cathy

Inconsistent time zone conversion in PostgreSQL

This question already has an answer here: Postgres UTC date format & epoch cast, sign inversion 1 answer That is actually described in some detail in the documentation: PostgreSQL allows you to specify time zones in three different forms: A full time zone name, for example America/New_York. [...] A time zone abbreviation, for example PST. [...] In addition to the timezone names and abbreviations, PostgreSQL will accept POSIX-style time zone specifications of the form STDoffs...

 postgresql                     1 answers                     46 view
 2018-10-22         Frederica

C/Postgres - Using palloc in a non-PG function

I'm currently implementing a user-defined data type - a linked list that mimics a set of integers in a file called intList.c, and hopefully used with intList.source to install it onto my Postgres server.So my questions are the following:Can I write C functions such as (link newNode) and (link InsertEnd) in my code wherein they're not postgres functions to be declared & created in the source file?Can I use palloc in a function called from my input function? (in this case link newNode)? Or should I do it in my input function? My code for intList.c is as follows: P.S. Thes...

 postgresql                     1 answers                     49 view
 2018-10-22         Benson

How do i divide my minute data into tables containing each month in Timescaledb (PostgreSQL extension)

I am new to timescaledb and I want to store one minute ohlcv ticks for each stock in the table. There are 1440 ticks generated daily for one stock and 43200 ticks a month. I have a 100 assets whose ticks I would like to store every month and basically have the tables divide every 30 days or so, so that I dont have to build complex logic for this division. Any suggestions on how this can be done with timescale DB.Currently, the way I am doing it isTake incoming tick (ex timestamp 1535090420)Round its timestamp to the nearest 30 day period (1535090420/(86400 * 30)) = 592.241...

 postgresql                     1 answers                     55 view
 2018-10-22         Orville

Nominatim european map indexing/import

I have installed nominatim on my local centOS 6.6 machine following the wiki.guide there http://wiki.openstreetmap.org/wiki/Nominatim/InstallationI follow the instructions line to line and I manage to import a little map (monaco-latest.osm.pbf) and everything goes fine. Now I tried to import the european map (15.8 gb), i follow the same procedure, I launch the command ./utils/setup.php --osm-file european.latest.osm.pbf --all --osm2pgsql-cache 2048 2>&1 | tee setup.log5 days ago and if I launch the command ps -aux | more I see the following output for nominatim/postg...

 postgresql                     1 answers                     56 view
 2018-10-22         Jacqueline

How to get executed client details inside PL/pgSQL function

could you please let me know, how can i get the function executed client(single client only) details inside the PL/pgSQL function(something $whoexecuted). i'm getting the current all active client details.as per the following code.beginselect ps.usename, ps.client_addr,ps.application_name from pg_stat_activity pswhere state = 'active'and ps.usename in('user1') into pg_stat_activity_rowtype; The content of pg_stat_activity is available from PLpgSQL too. So you can use do $$declare r record;begin for r in select * from pg_stat_activity where pid = pg_backend_pid() loop ...

 postgresql                     1 answers                     41 view
 2018-10-22         Dwight

Azure PostgreSQL suddenly uses all storage

Yesterday, an 'Azure Database for PostgreSQL server' database I have been appending to continually suddenly increased its rate of growth and used up all the storage, and the script reported errors as the connection became read-only.The queries for the database size on disk don't add up to anything like 18GB select pg_size_pretty(pg_database_size('database_alpha')) => '80 MB' select pg_size_pretty(pg_database_size('database_beta')) => '458 MB' select pg_size_pretty(pg_database_size('postgres')) => '8645 kB'If it's not my databases, what could be using the s...

 postgresql                     1 answers                     31 view
 2018-10-22         Evelyn

pg-promise - Not resolving multiple queries

I'm in the process of creating a mass mailer that allows a company to send out an email template to a specific plan that contains X amount of subscribers. However, when using pg-promise, I can't figure how to loop through an array that contains one or more unique plan names that'll then be used as query parameters for subsequent queries to gather a single array of results. The subsequent queries don't resolve before express sends back a specified error message.For example:const selectedTemplate = await db.oneOrNone("SELECT * FROM templates WHERE userid=$1 and uniqueTemplate...

 postgresql                     1 answers                     80 view
 2018-10-22         Gustave

node-postgres: multi-query is atomic?

When using pg-promise (based on node-postgres), a multi-query seems to be atomic.For example, the following PostgreSQL query does not insert any rows at all even though only the second INSERT fails due to a duplicate id. No transactions are used.insert into mytable (id) values (1); insert into mytable (id) values (1)This behavior seems counter-intuitive and differs from that of psql. Is this a bug? My tests indicate that yes, surprisingly, it is atomic, i.e. if one query fails, they all fail, same as inside a transaction.I will investigate why that is, and post an update...

 postgresql                     1 answers                     82 view
 2018-10-22         Honey

Batch INSERT on multiple queries is throwing foreign key violation

I am following this to do batch INSERTwith two queries. The first query inserts into <tableone> and the second query insert into <tabletwo>.The second table has a foreign key constraints that references <tableone>. The following code is how I am handling the batch insertsbatchQuery.push( insertTableOne, insertTableTwo);const query = pgp.helpers.concat(batchQuery);db.none(query)insertTableOne looks likeINSERT INTO tableone (id, att2, att3) VALUES (1, 'a', 'b'), (2, 'c', 'd'), (3, 'e', 'f'), ...insertTableTwo looks likeINSERT INTO tabletwo (id, tableone_id...

 postgresql                     1 answers                     84 view
 2018-10-22         Murphy

pg-promise - unable to receive db response back when use batch

I'm trying to get a response back from the database when I use tx.batch.Thanks.1) query db for pre-existing records that need to be updated 2) using results from 1, make another batch call to db to eithercreate or update records3) respond back with status 200 to my api with records created andrecords updatedBatch calls does work, verified records are inserted or/and updated into db. db.tx(t1 => { let queryCheck = []; reqData.forEach(obj => { for (let key in obj) { obj[key].resovledURLs.forEach(data => { queryCheck.push( t1.any(...

 postgresql                     1 answers                     85 view
 2018-10-22         Novia

Postgres HA (based on WAL-shipping) fails

I'm hoping someone can help me a WAL-shipping and warm standby issue. My standby system runs happily for weeks, then all of a sudden it starts looking for .history files that don't exist. It then craps out and I can't successfully restart it without rebuilding the standby. Both systems are running CentOS 4.5 and postgres 8.4.1. They use NFS to store WAL files from production on the standby.A relevant chunk of the log, with my comments:[** Recovery is running normally **]Trigger file : /tmp/pgsql.triggerWaiting for WAL file : 00000001000000830000005BWAL file pa...

 postgresql                     4 answers                     57 view
 2018-10-22         Matt

PowerBI Cx error connect to PostgreSQL

This is the error I received when attempting to connect to a PostgreSQL db using power biDetails: "TlsClientStream.ClientAlertException: CertificateExpired at TlsClientStream.TlsClientStream.SendAlertFatal(AlertDescriptiondescription, String message) at TlsClientStream.TlsClientStream.ParseCertificateMessage(Byte[]buf,Int32&poa) at TlsClientStream.TlsClientStream.TraverseHandchakeMessages() at TlsClientStream.TlsClientStream.GetInitialHandshakeMessages(Boolean allowApplicationData) at TlsClientStream.TlsClientStream.PerformIntitialHandshake(StringHostname, X50...

 postgresql                     2 answers                     70 view
 2018-10-22         Noah

Ident authentication failed for user "ckan_default"

I'm trying to get CKAN (latest - 2.8.1) running on RHEL 7 with Apache SOLR 7.4, Postgresql 9.2.23 and all the other requirements as whatever is the latest through yum.I have no issue creating the ckan_default user, and the database. But, when I get to this step:paster db init -c /etc/ckan/default/production.ini I get an authentication error. sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: Ident authentication failed for user "ckan_default"I thought perhaps I entered the password badly, but I've blown away postgres twice and very carefully entered the ck...

 postgresql                     1 answers                     47 view

Page 1 of 249  |  Show More Pages:  Top Prev Next Last